package cn.jbolt.core.db.sql;

import cn.hutool.core.stream.StreamUtil;
import cn.hutool.core.util.StrUtil;
import cn.jbolt.core.bean.JBoltDateRange;
import cn.jbolt.core.model.base.JBoltModelConfig;
import cn.jbolt.core.util.JBoltArrayUtil;
import cn.jbolt.core.util.JBoltDateUtil;
import cn.jbolt.core.util.JBoltRandomUtil;
import com.jfinal.kit.LogKit;
import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Model;

import java.io.Serializable;
import java.math.BigDecimal;
import java.sql.Time;
import java.util.*;
import java.util.Map.Entry;

/**
 * Sql语句拼接工具 面向对象方式
 *
 * @ClassName: Sql
 * @author: JFinal学院-小木 QQ：909854136
 * @date: 2019年9月5日
 */
@SuppressWarnings("serial")
public class Sql implements Serializable, Cloneable {
    /**
     * 内置常量 可用于其他数据库标识boolean true
     */
    public static final String TRUE = "1";
    /**
     * 内置常量 可用于其他数据库标识boolean false
     */
    public static final String FALSE = "0";
    /**
     * 数据库默认类型
     */
    protected String dbType = DBType.MYSQL;
    /**
     * 数据库schema
     */
    protected String schema;
    /**
     * 没有设置类型
     */
    public static final int TYPE_NONE = 0;
    /**
     * 查询搜索 select
     */
    public static final int TYPE_SELECT = 1;
    /**
     * 插入数据 sigle
     */
    public static final int TYPE_INSERT = 2;
    /**
     * 插入数据 batch
     */
    public static final int TYPE_INSERT_BATCH = 3;
    /**
     * 更新数据 update
     */
    public static final int TYPE_UPDATE = 4;
    /**
     * 删除数据 delete
     */
    public static final int TYPE_DELETE = 5;

    /************** 数据库关键字 *****************/

    public static final String KEY_SELECT = "select ";
    public static final String KEY_STAR = "*";
    public static final String KEY_QUESTION_MARK = "?";
    public static final String KEY_INSERT = "insert into ";
    public static final String KEY_UPDATE = "update ";
    public static final String KEY_DELETE = "delete ";
    public static final String KEY_LIMIT = " limit ";
    public static final String KEY_OFFSET = " offset ";
    public static final String KEY_ROWNUM_ORACLE = " rownum ";
    public static final String KEY_FROM = " from ";
    public static final String KEY_SET = " set ";
    public static final String KEY_WHERE = " where ";
    public static final String KEY_ORDERBY = " order by ";
    public static final String KEY_GROUPBY = " group by ";
    public static final String KEY_HAVING = " having ";
    public static final String KEY_WHITESPACE = " ";
    public static final String KEY_COMMA = ",";
    public static final String KEY_AS = " as ";
    public static final String KEY_AT = "@";
    public static final String KEY_ON = " on ";
    public static final String KEY_ASC = " asc";
    public static final String KEY_DESC = " desc";
    public static final String KEY_IF = "if";
    public static final String KEY_IF_BRACKETS_LEFT = "if(";
    public static final String KEY_IIF = "IIF";
    public static final String KEY_IIF_BRACKETS_LEFT = "IIF(";
    public static final String KEY_ASC_TRIMED = "asc";
    public static final String KEY_DESC_TRIMED = "desc";
    public static final String KEY_COUNT_START = "count(*)";
    public static final String KEY_COUNT_COLUMN = "count(%s)";
    public static final String KEY_MAX_COLUMN = "max(%s)";
    public static final String KEY_MIN_COLUMN = "min(%s)";
    public static final String KEY_SUM_COLUMN = "sum(%s)";
    public static final String KEY_AVG_COLUMN = "avg(%s)";
    public static final String KEY_DISTINCT = "distinct ";
    public static final String KEY_COUNT_DISTINCT = "count(distinct %s)";
    public static final String KEY_INSERT_VALUES = " values(%s) ";
    public static final String KEY_NULLSTRING = "";
    public static final String KEY_AND = " and ";
    public static final String KEY_AND_LEFT = " and";
    public static final String KEY_AND_RIGHT = "and ";
    public static final String KEY_BETWEEN = " between ";
    public static final String KEY_BETWEEN_LEFT = " between";
    public static final String KEY_BETWEEN_RIGHT = "between ";
    public static final String KEY_LEFT_JOIN = " left join ";
    public static final String KEY_LEFT_OUTER_JOIN = " left outer join ";
    public static final String KEY_RIGHT_JOIN = " right join ";
    public static final String KEY_RIGHT_OUTER_JOIN = " right outer join ";
    public static final String KEY_INNER_JOIN = " inner join ";
    public static final String KEY_FULL_JOIN = " full join ";
    public static final String KEY_ID = "id";
    public static final String KEY_ENABLE = "enable";
    public static final String KEY_IS_DELETED = "is_deleted";
    public static final String KEY_PID = "pid";
    public static final String KEY_SORT_RANK = "sort_rank";
    public static final String KEY_DOT = ".";
    public static final String KEY_DOT_WITH_SPLASH = "\\.";
    public static final String KEY_MYSQL_SINGLE_QUOTES = "`";
    public static final String KEY_SINGLE_QUOTES = "'";
    public static final String KEY_SINGLE_QUOTES_2 = "''";
    public static final String KEY_SQUARE_BRACKETS_LEFT = "[";
    public static final String KEY_SQUARE_BRACKETS_RIGHT = "]";
    public static final String KEY_BRACKET_LEFT = "(";
    public static final String KEY_BRACKET_RIGHT = ")";
    public static final String KEY_UNION = " union ";
    public static final String KEY_UNION_ALL = " union all ";
    public static final String KEY_RAND_MYSQL = " rand() ";
    public static final String KEY_RAND_DM = " rand() ";
    public static final String KEY_RAND_ORACLE = " DBMS_RANDOM.VALUE() ";
    public static final String KEY_RAND_POSTGRESQL = " random() ";
    public static final String KEY_NEWID_SQLSERVER = " NEWID() ";
    public static final String KEY_TOP1 = " top 1 ";
    public static final String KEY_EQUALS = " = ";
    public static final String KEY_EQUALS_TRIMED = "=";
    public static final String KEY_TABLENAME_ENDLINE = "_";
    public static final String KEY_MINUS = "-";
    public static final String KEY_PLUS = "+";
    public static final String KEY_LT = "<";
    public static final String KEY_GT = ">";
    public static final String KEY_QUOTATION_MARKS = "\"";

    // 语句操作类型
    private int type;
    // 表名
    private String table;
    // 搜索返回字段
    private String returnColumns;
    // distinct的字段
    private String distinctColumn;
    // 查询单列列表
    private boolean queryColumnList;
    // 查询数量
    private boolean queryCount;
    // 查询最大值
    private boolean queryMax;
    // 查询最小值
    private boolean queryMin;
    // 查询计算和
    private boolean querySum;
    // 查询计算平均数
    private boolean queryAvg;
    // 查询distinct数量
    private boolean queryDistinctCount;
    // 查询distinct
    private boolean queryDistinct;
    // where后的条件
    private final List<Condition> conditions;
    // insert values
    private Object[] insertValues;
    // 需要更新的字段map
    private LinkedHashMap<String, Object> updateColumnsMap;
    // 分组查询
    private String[] groupBys;
    // having
    private SqlExpress havingSqlExpress;
    // 排序查询多个构建
    private List<OrderBy> orderBys;
    // 关联查询语句
    private List<String> joinSqls;
    // 查询数量 字段
    private String countColumns;
    // prepared sql需要的值
    private List<Object> whereValues;
    // 是否支持问号占位方式
    private boolean prepared;
    // 是否启用分页
    private boolean hasPage;
    // 分页参数 第几页
    private int pageNumber;
    // 分页参数 每页几个
    private int pageSize;
    //union的sql对象
    private List<UnionSql> unionSqls;
    //主表的asName
    private String mainTableAsName;
    //查询的执行列
    private String[] columns;
    //是否检测where条件有没有数据 默认检测 检测不到就报错 只有update和delete操作需要检测
    private boolean checkWhereExists;

    /**
     * 只能从这里获得Sql 指定数据类型
     *
     * @return
     */
    public static Sql me(String dbType) {
        return new Sql().setDbType(dbType);
    }

    /**
     * 支持问号占位sql
     *
     * @return
     */
    public Sql prepared() {
        this.prepared = true;
        return this;
    }

    /**
     * 首页
     *
     * @param pageSize
     * @return
     */
    public Sql firstPage(int pageSize) {
        return page(1, pageSize);
    }

    /**
     * 限制了查询一个
     *
     * @return
     */
    public boolean isLimitOne() {
        return this.pageSize == 1;
    }

    /**
     * 分页查询
     *
     * @param pageNumber
     * @param pageSize
     * @return
     */
    public Sql page(int pageNumber, int pageSize) {
        if (pageNumber <= 0) {
            throw new RuntimeException("Sql pageNumber must >0");
        }
        if (pageSize <= 0) {
            throw new RuntimeException("Sql PageSize must >0");
        }
        this.hasPage = true;
        this.pageNumber = pageNumber;
        this.pageSize = pageSize;
        if (pageNumber == 1 && pageSize == 1 && DBType.SQLSERVER.equalsIgnoreCase(dbType)) {
            this.returnColumns = KEY_TOP1 + returnColumns;
        }
        return this;
    }

    /**
     * 分页查询 符合条件第一个
     *
     * @return
     */
    public Sql first() {
        return page(1, 1);
    }

    /**
     * 随机一个
     *
     * @return
     */
    public Sql randomOne() {
        processRandom();
        return page(1, 1);
    }

    /**
     * 随机指定个数
     *
     * @param count
     * @return
     */
    public Sql random(int count) {
        processRandom();
        return page(1, count);
    }

    /**
     * 处理random
     */
    private void processRandom() {
        switch (dbType) {
            case DBType.MYSQL:
                asc(KEY_RAND_MYSQL);
                break;
            case DBType.DM:
                asc(KEY_RAND_DM);
                break;
            case DBType.ORACLE:
                asc(KEY_RAND_ORACLE);
                break;
            case DBType.POSTGRESQL:
                asc(KEY_RAND_POSTGRESQL);
                break;
            case DBType.SQLSERVER:
                asc(KEY_NEWID_SQLSERVER);
                break;
            default:
                asc(KEY_RAND_MYSQL);
                break;
        }
    }

    /**
     * 构造函数
     */
    private Sql() {
        this.type = TYPE_NONE;
        this.returnColumns = KEY_STAR;
        this.conditions = new ArrayList<>();
        //设置是否检测Where条件是否存在 如果不存在就报错 默认检测
        this.checkWhereExists = true;
    }

    /**
     * 查询表
     *
     * @param table
     * @param asName
     * @return
     */
    public Sql from(String table, String asName) {
        if (StrKit.hasBlank(table, asName)) {
            throw new RuntimeException("from params has blank value");
        }
        this.table = table;
        this.mainTableAsName = asName;
        return this;
    }

    /**
     * 查询表
     *
     * @param tableSql
     * @param asName
     * @return
     */
    public Sql fromSql(String tableSql, String asName) {
        if (StrKit.hasBlank(tableSql, asName)) {
            throw new RuntimeException("fromSql params has blank value");
        }
        if (asName == null || asName.trim().isEmpty()) {
            if (tableSql.contains(KEY_FROM)) {
                asName = "jtb_tmp_" + JBoltRandomUtil.randomNumber(3);
            } else {
                asName = "this";
            }
        }
        this.table = KEY_BRACKET_LEFT + tableSql + KEY_BRACKET_RIGHT;
        this.mainTableAsName = asName;
        return this;
    }

    /**
     * 查询 sql返回值
     *
     * @param sql
     * @param asName
     * @return
     */
    public Sql from(Sql sql, String asName) {
        if (sql == null) {
            throw new RuntimeException("from sql is null");
        }
        if (asName == null || asName.trim().isEmpty()) {
            if (sql.table != null) {
                asName = "jtb_tmp_" + JBoltRandomUtil.randomNumber(3);
            } else {
                asName = "this";
            }
        }
        this.table = KEY_BRACKET_LEFT + sql.toSql() + KEY_BRACKET_RIGHT;
        this.mainTableAsName = asName;
        return this;
    }

    /**
     * 查询 sql返回值
     *
     * @param sql
     * @return
     */
    public Sql from(Sql sql) {
        if (sql == null) {
            throw new RuntimeException("from sql is null");
        }
        return from(sql, null);
    }

    /**
     * 判断是Mysql
     *
     * @return
     */
    public boolean isMysql() {
        return DBType.isMysql(dbType);
    }

    /**
     * 判断是达梦
     *
     * @return
     */
    public boolean isDM() {
        return DBType.isDM(dbType);
    }

    /**
     * 判断是Postgresql
     *
     * @return
     */
    public boolean isPostgresql() {
        return DBType.isPostgresql(dbType);
    }

    /**
     * 判断是Oracle
     *
     * @return
     */
    public boolean isOracle() {
        return DBType.isOracle(dbType);
    }

    /**
     * 判断是SqlServer
     *
     * @return
     */
    public boolean isSqlServer() {
        return DBType.isSqlServer(dbType);
    }


    /**
     * from表
     *
     * @param table
     * @return
     */
    public Sql from(String table) {
        if (StrKit.isBlank(table)) {
            throw new RuntimeException("from table is null");
        }
        this.table = table;
        this.mainTableAsName = null;
        return this;
    }

    /**
     * from表
     *
     * @param table
     * @param keepAsName
     * @return
     */
    public Sql from(String table, boolean ignoreIfHasTable, boolean keepAsName) {
        if (ignoreIfHasTable && hasTable()) {
            return this;
        }
        if (StrKit.isBlank(table)) {
            throw new RuntimeException("from table is null");
        }
        this.table = table;
        if (!keepAsName) {
            this.mainTableAsName = null;
        }
        return this;
    }

    /**
     * 查询表 依赖model映射
     *
     * @param modelClass
     * @return
     */
    @SuppressWarnings("rawtypes")
    public Sql from(Class<? extends Model> modelClass) {
        return from(processTableName(modelClass));
    }

    /**
     * 处理tableName
     *
     * @param modelClass
     * @return
     */
    private String processTableName(Class<? extends Model> modelClass) {
        return JBoltModelConfig.me.processTableName(modelClass);
    }

    /* */

    /**
     * 根据数据库类型处理名称
     *
     * @param name
     * @return
     *//*
    private String processNameByDbType(String name) {
        if (name.contains(KEY_STAR) || name.contains(KEY_BRACKET_LEFT) || name.contains(KEY_MYSQL_SINGLE_QUOTES) || name.contains(KEY_SINGLE_QUOTES) || name.contains(KEY_SQUARE_BRACKETS_LEFT) || name.contains(KEY_QUOTATION_MARKS) || name.contains(KEY_AT)){
            return name;
        }
        if(name.contains(KEY_COMMA)){
            String[] arr = JBoltArrayUtil.from(name);
            int size =arr.length;
            for(int i=0;i<size;i++){
                arr[i] = processNameByDbType(arr[i]);
            }
            return JBoltArrayUtil.join(arr);
        }

        // name as xxx
        if(name.contains(KEY_AS)){
            String[] arr = JBoltArrayUtil.from(name,KEY_AS);
            arr[0] = processNameByDbType(arr[0]);
            return JBoltArrayUtil.join(arr,KEY_AS);
        }

        // xxx=yyy and zzz=aaa
        if(name.contains(KEY_AND) && name.contains(KEY_EQUALS_TRIMED) && !name.contains(KEY_BETWEEN)){
            String[] arr = JBoltArrayUtil.from(name,KEY_AND);
            int size =arr.length;
            for(int i=0;i<size;i++){
                arr[i] = processNameByDbType(arr[i]);
            }
            return JBoltArrayUtil.join(arr,KEY_AND);
        }

        // xxx=yyy
        if(name.contains(KEY_EQUALS_TRIMED)){
            String[] arr = JBoltArrayUtil.from(name,KEY_EQUALS_TRIMED);
            int size =arr.length;
            for(int i=0;i<size;i++){
                arr[i] = processNameByDbType(arr[i]);
            }
            return JBoltArrayUtil.join(arr,KEY_EQUALS_TRIMED);
        }

        // name between xxx and yyy
        if(name.contains(KEY_BETWEEN)&&name.contains(KEY_AND)){
            String[] arr = new String[3];
            arr[0] = processNameByDbType(name.substring(0,name.indexOf(KEY_BETWEEN_LEFT)).trim());
            arr[1] = processNameByDbType(name.substring(name.indexOf(KEY_BETWEEN_RIGHT)+8,name.indexOf(KEY_AND_LEFT)).trim());
            arr[2] = processNameByDbType(name.substring(name.indexOf(KEY_AND_RIGHT)+4).trim());
            return arr[0] + KEY_BETWEEN +arr[1] + KEY_AND + arr[2];
        }



        // distinct xxx
        if(name.contains(KEY_DISTINCT)){
            String[] arr = JBoltArrayUtil.from(name,KEY_WHITESPACE);
            arr[1] = processNameByDbType(arr[1]);
            return JBoltArrayUtil.join(arr,KEY_WHITESPACE);
        }

        // xxx.yyy
        if(name.contains(KEY_DOT)){
            String[] arr = JBoltArrayUtil.from(name,KEY_DOT_WITH_SPLASH);
            arr[1] = processTheNameByDbType(arr[1]);
            return JBoltArrayUtil.join(arr,KEY_DOT);
        }

        return processTheNameByDbType(name);
    }
*/
    private String processColumnNameByDbType(String name) {
        if (name.equals(KEY_STAR) || name.contains(KEY_SINGLE_QUOTES) || name.contains(KEY_IF_BRACKETS_LEFT) || name.contains(KEY_IIF_BRACKETS_LEFT) || name.contains(KEY_BRACKET_LEFT) || name.contains(KEY_AT)) {
            return name;
        }

        if (name.contains(KEY_COMMA)) {
            String[] arr = JBoltArrayUtil.from(name);
            int size = arr.length;
            for (int i = 0; i < size; i++) {
                arr[i] = processColumnNameByDbType(arr[i]);
            }
            return JBoltArrayUtil.join(arr);
        }

        // name as xxx
        if (name.contains(KEY_AS)) {
            String[] arr = JBoltArrayUtil.from(name, KEY_AS);
            arr[0] = processColumnNameByDbType(arr[0]);
            return JBoltArrayUtil.join(arr, KEY_AS);
        }

        // distinct xxx
        if (name.contains(KEY_DISTINCT)) {
            String[] arr = JBoltArrayUtil.from(name, KEY_WHITESPACE);
            arr[1] = processColumnNameByDbType(arr[1]);
            return JBoltArrayUtil.join(arr, KEY_WHITESPACE);
        }

        // xxx.yyy
        if (name.contains(KEY_DOT)) {
            String[] arr = JBoltArrayUtil.from(name, KEY_DOT_WITH_SPLASH);
            arr[1] = processColumnNameByDbType(arr[1]);
            return JBoltArrayUtil.join(arr, KEY_DOT);
        }

        switch (dbType) {
            case DBType.MYSQL:
                if (name.contains(KEY_MYSQL_SINGLE_QUOTES)) {
                    return name;
                }
                return KEY_MYSQL_SINGLE_QUOTES + name + KEY_MYSQL_SINGLE_QUOTES;
            case DBType.SQLSERVER:
                if (name.contains(KEY_SQUARE_BRACKETS_LEFT)) {
                    return name;
                }
                return KEY_SQUARE_BRACKETS_LEFT + name + KEY_SQUARE_BRACKETS_RIGHT;
            case DBType.ORACLE:
                break;
            case DBType.POSTGRESQL:
            case DBType.DM:
                // name as xxx
                if (name.contains(KEY_QUOTATION_MARKS)) {
                    return name;
                }
                return KEY_QUOTATION_MARKS + name + KEY_QUOTATION_MARKS;
            default:
                break;
        }
        return name;
    }

    /**
     * 查询表
     *
     * @param modelClass
     * @param asName
     * @return
     */
    @SuppressWarnings("rawtypes")
    public Sql from(Class<? extends Model> modelClass, String asName) {
        return from(processTableName(modelClass), asName);
    }

    /**
     * 查询表
     *
     * @param table
     * @return
     */
    public Sql fromIfBlank(String table) {
        return fromIfBlank(table, true);
    }

    /**
     * 查询表
     *
     * @param table
     * @param keepAsName
     * @return
     */
    public Sql fromIfBlank(String table, boolean keepAsName) {
        return from(table, true, keepAsName);
    }

    /**
     * columnName is null 可变数量参数
     *
     * @param columnNames
     * @return
     */
    public Sql isNull(String... columnNames) {
        if (columnNames == null || columnNames.length == 0) {
            return this;
        }
        for (String columnName : columnNames) {
            if (StrKit.notBlank(columnName)) {
                processAnd();
                conditions.add(new Condition().isNull(processPrependMainTableAsName(columnName)));
            }
        }
        return this;
    }

    /**
     * columnName is not null 可变数量参数
     *
     * @param columnNames
     * @return
     */
    public Sql isNotNull(String... columnNames) {
        if (columnNames == null || columnNames.length == 0) {
            return this;
        }
        for (String columnName : columnNames) {
            if (StrKit.notBlank(columnName)) {
                processAnd();
                conditions.add(new Condition().isNotNull(processPrependMainTableAsName(columnName)));
            }
        }
        return this;
    }

    /**
     * 设置insert values
     *
     * @param values
     * @return
     */
    public Sql values(Object... values) {
        if (values == null || values.length == 0) {
            throw new RuntimeException("insert values is null");
        }
        this.insertValues = values;
        return this;
    }

    /**
     * 更新表
     *
     * @param table
     * @return
     */
    public Sql update(String table) {
        if (StrKit.isBlank(table)) {
            throw new RuntimeException("update table is null");
        }
        this.table = table;
        this.type = TYPE_UPDATE;
        return this;
    }


    /**
     * 插入数据
     *
     * @param tableAndColumns
     * @return
     */
    public Sql insert(String tableAndColumns) {
        if (StrKit.isBlank(tableAndColumns)) {
            throw new RuntimeException("insert tableAndColumns is null");
        }
        this.table = tableAndColumns;
        this.type = TYPE_INSERT;
        return this;
    }

    /**
     * 插入数据
     *
     * @param table
     * @param insertColumns
     * @return
     */
    public Sql insert(String table, String insertColumns) {
        if (StrKit.isBlank(table)) {
            throw new RuntimeException("insert table is null");
        }
        if (StrKit.isBlank(insertColumns)) {
            throw new RuntimeException("insert insertColumns is null");
        }
        this.table = table + KEY_BRACKET_LEFT + insertColumns + KEY_BRACKET_RIGHT;
        this.type = TYPE_INSERT;
        return this;
    }

    /**
     * 插入数据
     *
     * @param table
     * @param insertColumns
     * @param values
     * @return
     */
    public Sql insert(String table, String insertColumns, Object... values) {
        if (StrKit.isBlank(table)) {
            throw new RuntimeException("insert table is null");
        }
        if (StrKit.isBlank(insertColumns)) {
            throw new RuntimeException("insert insertColumns is null");
        }
        values(values);
        this.table = table + KEY_BRACKET_LEFT + insertColumns + KEY_BRACKET_RIGHT;
        this.type = TYPE_INSERT;
        return this;
    }

    /**
     * 删除表数据
     *
     * @return
     */
    public Sql delete() {
        this.type = TYPE_DELETE;
        return this;
    }

    /**
     * 删除表数据
     *
     * @param table
     * @return
     */
    public Sql delete(String table) {
        this.type = TYPE_DELETE;
        return from(table);
    }

    /**
     * 查询ID
     *
     * @return
     */
    public Sql selectId() {
        return select(KEY_ID);
    }

    /**
     * 追加查询返回结果 returnColumns
     *
     * @param columns
     * @return
     */
    public Sql selectAppend(String... columns) {
        this.type = TYPE_SELECT;
        this.queryColumnList = false;
        //如果传的是空 直接返回
        if (columns == null || columns.length == 0) {
            if (StrKit.isBlank(this.returnColumns)) {
                this.returnColumns = processPrependMainTableAsName(KEY_STAR);
                this.columns = new String[]{this.returnColumns};
            }
            return this;
        }
        int size = columns.length;
        if (StrKit.isBlank(this.returnColumns)) {
            this.returnColumns = "";
        } else {
            this.returnColumns += KEY_COMMA;
        }
        for (int i = 0; i < size; i++) {
            this.returnColumns += processPrependMainTableAsName(columns[i]);
            if (i != size - 1) {
                this.returnColumns += KEY_COMMA;
            }
        }
        this.columns = JBoltArrayUtil.from(this.returnColumns);
        return this;
    }

    /**
     * 前插查询返回结果 returnColumns
     *
     * @param columns
     * @return
     */
    public Sql selectPrepend(String... columns) {
        this.type = TYPE_SELECT;
        this.queryColumnList = false;
        //如果传的是空 直接返回
        if (columns == null || columns.length == 0) {
            if (StrKit.isBlank(this.returnColumns)) {
                this.returnColumns = processPrependMainTableAsName(KEY_STAR);
                this.columns = new String[]{this.returnColumns};
            }
            return this;
        }
        int size = columns.length;

        String prepend = "";
        for (int i = 0; i < size; i++) {
            prepend += processPrependMainTableAsName(columns[i]);
            if (i != size - 1) {
                prepend += KEY_COMMA;
            }
        }
        if (StrKit.isBlank(this.returnColumns)) {
            this.returnColumns = prepend;
        } else {
            this.returnColumns = prepend + KEY_COMMA + this.returnColumns;
        }
        this.columns = JBoltArrayUtil.from(this.returnColumns);
        return this;
    }

    /**
     * 搜索表中的指定列
     *
     * @param columns
     * @return
     */
    public Sql select(List<String> columns) {
        if (columns == null || columns.size() == 0) {
            return select(KEY_STAR);
        }
        return select(columns.toArray(new String[0]));
    }

    /**
     * 搜索表中的指定列
     *
     * @param columns
     * @return
     */
    public Sql select(String... columns) {
        this.type = TYPE_SELECT;
        // 如果是null 直接按照* 查询
        if (columns == null || (columns.length == 1 && columns[0] == null)) {
            this.queryColumnList = false;
            this.returnColumns = processPrependMainTableAsName(KEY_STAR);
            this.columns = new String[]{this.returnColumns};
            return this;
        }
        // 判断查询columns个数
        int size = columns.length;
        if (size == 0) {
            // 没有就按照星
            this.queryColumnList = false;
            this.returnColumns = processPrependMainTableAsName(KEY_STAR);
            this.columns = new String[]{this.returnColumns};
            return this;
        }
        if (size == 1) {
            String firstColumns = columns[0];
            // 一个column 可能是单个列查询列表 如果带着表达式 可能是查询表达式结果
            this.queryColumnList = true;
            this.returnColumns = processPrependMainTableAsName(firstColumns);
            this.columns = new String[]{this.returnColumns};
        } else {
            this.queryColumnList = false;
            // 多个肯定就是分开了
            this.returnColumns = "";
            for (int i = 0; i < size; i++) {
                this.returnColumns += processPrependMainTableAsName(columns[i]);
                if (i != size - 1) {
                    this.returnColumns += KEY_COMMA;
                }
            }
            this.columns = JBoltArrayUtil.from(this.returnColumns);
        }
        return this;
    }

    /**
     * 条件 or
     *
     * @return
     */
    public Sql or() {
        conditions.add(new Condition().or());
        return this;
    }

    /**
     * 条件 and
     *
     * @return
     */
    public Sql and() {
        conditions.add(new Condition().and());
        return this;
    }

    /**
     * 条件 in(数组值) list
     *
     * @param columnName
     * @param inValues
     * @return
     */
    public Sql inList(String columnName, List<?> inValues) {
        return inList(columnName, inValues, true);
    }

    /**
     * 条件 in(数组值) list
     *
     * @param columnName
     * @param inValues
     * @param ignoreIfValueIsBlank
     * @return
     */
    public Sql inList(String columnName, List<?> inValues, boolean ignoreIfValueIsBlank) {
        if (inValues == null || inValues.size() == 0) {
            if (ignoreIfValueIsBlank) {
                return this;
            }
            throw new RuntimeException("sql inList 'inValues' is null or empty");
        }
        processAnd();
        conditions.add(new Condition().in(processPrependMainTableAsName(columnName), inValues.toArray(new Object[inValues.size()])));
        return this;
    }

    /**
     * 条件 in(数组值)
     * 默认如果inValues空 就忽略
     *
     * @param columnName
     * @param inValues
     * @return
     */
    @SuppressWarnings("rawtypes")
    public Sql in(String columnName, Object... inValues) {
        return in(columnName, true, inValues);
    }

    /**
     * 条件 in(数组值)
     *
     * @param columnName
     * @param ignoreIfValueIsBlank
     * @param inValues
     * @return
     */
    @SuppressWarnings("rawtypes")
    public Sql in(String columnName, boolean ignoreIfValueIsBlank, Object... inValues) {
        if (inValues == null || inValues.length == 0) {
            if (ignoreIfValueIsBlank) {
                return this;
            }
            throw new RuntimeException("sql in 'inValues' is null or empty");
        }
        if (inValues.length == 1 && inValues[0] instanceof List) {
            return inList(columnName, (List) inValues[0], ignoreIfValueIsBlank);
        }
        processAnd();
        conditions.add(new Condition().in(processPrependMainTableAsName(columnName), inValues));
        return this;
    }

    /**
     * 条件 not in(数组值)
     *
     * @param columnName
     * @param notInValues
     * @return
     */
    public Sql notIn(String columnName, Object... notInValues) {
        return notIn(columnName, true, notInValues);
    }

    /**
     * 条件 not in(数组值)
     *
     * @param columnName
     * @param ignoreIfValueIsBlank
     * @param notInValues
     * @return
     */
    public Sql notIn(String columnName, boolean ignoreIfValueIsBlank, Object... notInValues) {
        if (notInValues == null || notInValues.length == 0) {
            if (ignoreIfValueIsBlank) {
                return this;
            }
            throw new RuntimeException("sql notIn 'notInValues' is null or empty");
        }
        processAnd();
        conditions.add(new Condition().notIn(processPrependMainTableAsName(columnName), notInValues));
        return this;
    }

    /**
     * 条件 in(数组字符串)
     *
     * @param columnName
     * @param inValuesStr
     * @return
     */
    public Sql in(String columnName, String inValuesStr) {
        return in(columnName, inValuesStr, true);
    }

    /**
     * 条件 in(数组字符串)
     *
     * @param columnName
     * @param inValuesStr
     * @param ignoreIfValueIsBlank
     * @return
     */
    public Sql in(String columnName, String inValuesStr, boolean ignoreIfValueIsBlank) {
        if (StrKit.isBlank(columnName)) {
            throw new RuntimeException("sql in columnName is Null");
        }
        if (StrKit.isBlank(inValuesStr)) {
            if (ignoreIfValueIsBlank) {
                return this;
            }
            throw new RuntimeException("sql in 'inValuesStr' is null or empty");
        }
        processAnd();
        conditions.add(new Condition().in(processPrependMainTableAsName(columnName), inValuesStr));
        return this;
    }

    /**
     * 条件 instr查询
     *
     * @param str
     * @param substr
     * @param strIsTableColumn
     * @return
     */
    public Sql instr(String str, String substr, boolean strIsTableColumn) {
        if (StrKit.hasBlank(str, substr)) {
            return this;
        }
        processAnd();
        conditions.add(new Condition().instr(strIsTableColumn ? processPrependMainTableAsName(str) : str, substr, strIsTableColumn));
        return this;
    }

    /**
     * 条件 notinstr查询
     *
     * @param str
     * @param substr
     * @param strIsTableColumn
     * @return
     */
    public Sql notInstr(String str, String substr, boolean strIsTableColumn) {
        if (StrKit.hasBlank(str, substr)) {
            return this;
        }
        processAnd();
        conditions.add(new Condition().notInstr(strIsTableColumn ? processPrependMainTableAsName(str) : str, substr, strIsTableColumn));
        return this;
    }

    /**
     * 条件 not in(数组字符串)
     *
     * @param columnName
     * @param notInValues
     * @return
     */
    public Sql notIn(String columnName, String notInValues) {
        return notIn(columnName, notInValues, true);
    }

    /**
     * 条件 not in(数组字符串)
     *
     * @param columnName
     * @param notInValuesStr
     * @param ignoreIfValueIsBlank
     * @return
     */
    public Sql notIn(String columnName, String notInValuesStr, boolean ignoreIfValueIsBlank) {
        if (StrKit.isBlank(columnName)) {
            throw new RuntimeException("sql notIn columnName is Null");
        }
        if (StrKit.isBlank(notInValuesStr)) {
            if (ignoreIfValueIsBlank) {
                return this;
            }
            throw new RuntimeException("sql notIn notInValuesStr' is null or empty");
        }
        processAnd();
        conditions.add(new Condition().notIn(processPrependMainTableAsName(columnName), notInValuesStr));
        return this;
    }

    /**
     * 条件 not find_in_set
     *
     * @param value
     * @param values
     * @param valuesIsTableColumn values参数是否是数据库表中的列
     * @return
     */
    public Sql notFindInSet(Object value, String values, boolean valuesIsTableColumn) {
        if (value == null || StrKit.isBlank(values)) {
            return this;
        }
        switch (dbType) {
            case DBType.MYSQL:
                processAnd();
                conditions.add(new Condition().notFindInSet(value, valuesIsTableColumn ? processPrependMainTableAsName(values) : values, valuesIsTableColumn));
                break;
            case DBType.SQLSERVER:
                notLike("(',' + " + (valuesIsTableColumn ? processPrependMainTableAsName(values) : safeValue(values)) + " + ',')", "," + value.toString() + ",");
                break;
            case DBType.POSTGRESQL:
            case DBType.ORACLE:
            case DBType.DM:
                notLike("(',' || " + (valuesIsTableColumn ? processPrependMainTableAsName(values) : safeValue(values)) + " ||',')", "," + value.toString() + ",");
                break;
        }
        return this;
    }

    /**
     * 条件 find_in_set
     *
     * @param value               匹配值
     * @param values              跟谁匹配
     * @param valuesIsTableColumn values参数是否是数据库表中的列
     * @return
     */
    public Sql findInSet(Object value, String values, boolean valuesIsTableColumn) {
        if (value == null || StrKit.isBlank(values)) {
            return this;
        }
        switch (dbType) {
            case DBType.MYSQL:
                processAnd();
                conditions.add(new Condition().findInSet(value, valuesIsTableColumn ? processPrependMainTableAsName(values) : values, valuesIsTableColumn));
                break;
            case DBType.SQLSERVER:
                like("(',' + " + (valuesIsTableColumn ? processPrependMainTableAsName(values) : safeValue(values)) + " + ',')", "," + value.toString() + ",");
                break;
            case DBType.POSTGRESQL:
            case DBType.ORACLE:
            case DBType.DM:
                like("(',' || " + (valuesIsTableColumn ? processPrependMainTableAsName(values) : safeValue(values)) + " ||',')", "," + value.toString() + ",");
                break;
        }
        return this;
    }

    /**
     * 条件 in(返回数组值的sql对象)
     *
     * @param columnName
     * @param sql
     * @return
     */
    public Sql inSql(String columnName, Sql sql) {
        return inSql(columnName, sql.toSql());
    }

    /**
     * 条件 not in(返回数组值的sql对象)
     *
     * @param columnName
     * @param sql
     * @return
     */
    public Sql notInSql(String columnName, Sql sql) {
        return notInSql(columnName, sql.toSql());
    }

    /**
     * 条件 in(返回数组值的sql语句)
     *
     * @param columnName
     * @param sql
     * @return
     */
    public Sql inSql(String columnName, String sql) {
        if (StrKit.hasBlank(columnName, sql)) {
            return this;
        }
        processAnd();
        conditions.add(new Condition().inSql(processPrependMainTableAsName(columnName), sql));
        return this;
    }

    /**
     * 条件 not in(返回数组值的sql语句)
     *
     * @param columnName
     * @param sql
     * @return
     */
    public Sql notInSql(String columnName, String sql) {
        if (StrKit.hasBlank(columnName, sql)) {
            return this;
        }
        processAnd();
        conditions.add(new Condition().notInSql(processPrependMainTableAsName(columnName), sql));
        return this;
    }

    /**
     * 数字区间比较 columnName>=leftValue and  columnName<=rightValue
     *
     * @param columnName
     * @param leftValue
     * @param rightValue
     * @return
     */
    public Sql between(String columnName, Integer leftValue, Integer rightValue) {
        if (StrKit.isBlank(columnName)) {
            return this;
        }
        ge(columnName, leftValue);
        le(columnName, rightValue);
        return this;
    }

    /**
     * 数字区间比较 columnName>=leftValue and  columnName<=rightValue
     *
     * @param columnName
     * @param leftValue
     * @param rightValue
     * @return
     */
    public Sql between(String columnName, BigDecimal leftValue, BigDecimal rightValue) {
        if (StrKit.isBlank(columnName)) {
            return this;
        }
        ge(columnName, leftValue);
        le(columnName, rightValue);
        return this;
    }

    /**
     * 时间区间 columnName >= startDateTime and columnName <= endDateTime
     *
     * @param columnName
     * @param dateRange
     * @return
     */
    public Sql betweenDateRange(String columnName, JBoltDateRange dateRange) {
        if (StrKit.isBlank(columnName) || dateRange == null) {
            return this;
        }
        String type = dateRange.getType();
        switch (type) {
            case JBoltDateRange.TYPE_DATE:
                bwDate(columnName, dateRange.getStartDate(), dateRange.getEndDate());
                break;
            case JBoltDateRange.TYPE_DATETIME:
                bwDateTime(columnName, dateRange.getStartDateTime(), dateRange.getEndDateTime());
                break;
            default:
                throw new RuntimeException("目前只支持Date 和 DateTime 两种类型");
        }
        return this;
    }


    /**
     * 时间比较 columnName >= startDate and columnName <= endDate
     *
     * @param columnName
     * @param startDate
     * @param endDate
     */
    public Sql bwDate(String columnName, Date startDate, Date endDate) {
        if (startDate != null) {
            ge(columnName, toStartDateTime000000(startDate));
        }
        if (endDate != null) {
            le(columnName, toEndDateTime235959(endDate));
        }
        return this;
    }

    /**
     * 时间比较 columnName >= startDateTime and columnName <= endDateTime
     *
     * @param columnName
     * @param startDateTime
     * @param endDateTime
     * @return
     */
    public Sql bwDateTime(String columnName, Date startDateTime, Date endDateTime) {
        if (startDateTime != null) {
            ge(columnName, toStartDateTime(startDateTime));
        }
        if (endDateTime != null) {
            le(columnName, toEndDateTime(endDateTime));
        }
        return this;
    }

    /**
     * 转为DateTime
     *
     * @param datetime
     * @return
     */
    public Object toDateTime(Date datetime) {
        return toDateTime(JBoltDateUtil.format(datetime, JBoltDateUtil.YMDHMS));
    }

    /**
     * 转为DateTime
     *
     * @param time
     * @return
     */
    public Object toTime(Time time) {
        return toTime(JBoltDateUtil.getTime(time));
    }

    /**
     * 转为startDateTime
     *
     * @param startDateTime
     * @return
     */
    public Object toStartDateTime(Date startDateTime) {
        return toDateTime(JBoltDateUtil.format(startDateTime, JBoltDateUtil.YMDHMS));
    }

    /**
     * 转为startDateTime 时分秒为0
     *
     * @param startDate
     * @return
     */
    public Object toStartDateTime000000(Date startDate) {
        return toDateTime(JBoltDateUtil.HHmmssTo000000Str(startDate));
    }

    /**
     * 转为endDateTime
     *
     * @param endDateTime
     * @return
     */
    public Object toEndDateTime(Date endDateTime) {
        return toDateTime(JBoltDateUtil.format(endDateTime, JBoltDateUtil.YMDHMS));
    }

    /**
     * 转为endDateTime 时分秒转为235959
     *
     * @param endDate
     * @return
     */
    public Object toEndDateTime235959(Date endDate) {
        return toDateTime(JBoltDateUtil.HHmmssTo235959Str(endDate));
    }

    /**
     * 转为表达式处理date
     *
     * @param dateTime
     * @return
     */
    public Object toDateTime(String dateTime) {
        if (StrKit.isBlank(dateTime)) {
            return null;
        }
        if (isOracle()) {
            return new SqlExpress(String.format("TO_DATE('%s','yyyy-mm-dd hh24:mi:ss')", dateTime));
        }
        return dateTime;
    }

    /**
     * 转为表达式处理time
     *
     * @param time
     * @return
     */
    public Object toTime(String time) {
        if (StrKit.isBlank(time)) {
            return null;
        }
        if (isOracle()) {
            return new SqlExpress(String.format("TO_CHAR('%s','hh24:mi:ss')", time));
        }
        return time;
    }

    /**
     * 条件 columnName=value
     *
     * @param columnName
     * @param value
     * @param ignoreIfValueIsNull
     * @return
     */
    public Sql eq(String columnName, Object value, boolean ignoreIfValueIsNull) {
        if (StrKit.isBlank(columnName)) {
            throw new RuntimeException("sql eq 'columnName' is null");
        }
        if (value == null) {
            if (ignoreIfValueIsNull) {
                return this;
            }
            throw new RuntimeException("sql eq 'value' is null");
        }
        processEq(columnName, value);
        return this;
    }

    /**
     * 条件 columnName=value
     *
     * @param columnName
     * @param value
     * @return
     */
    public Sql eq(String columnName, Object value) {
        return eq(columnName, value, true);
    }

    /**
     * 条件 enable=value
     *
     * @param enable
     * @return
     */
    public Sql enableEq(Boolean enable) {
        return eqBooleanToChar(KEY_ENABLE, enable);
    }

    /**
     * 条件 enable=true
     *
     * @return
     */
    public Sql enable() {
        return enableEq(true);
    }

    /**
     * 条件 isDeleted=value
     *
     * @param isDeleted
     * @return
     */
    public Sql isDeletedEq(Boolean isDeleted) {
        return eqBooleanToChar(KEY_IS_DELETED, isDeleted);
    }

    /**
     * 条件 columnName=value
     *
     * @param columnName
     * @param value
     * @return
     */
    public Sql eqDate(String columnName, Date value) {
        return eqDate(columnName, value, true);
    }

    /**
     * 条件 columnName=value
     *
     * @param columnName
     * @param value
     * @param ignoreIfValueIsNull
     * @return
     */
    public Sql eqDate(String columnName, Date value, boolean ignoreIfValueIsNull) {
        if (value == null) {
            if (ignoreIfValueIsNull) {
                return this;
            }
            throw new RuntimeException("sql eqDate 'value' is null");
        }
        processEq(columnName, JBoltDateUtil.format(value, JBoltDateUtil.YMD));
        return this;
    }

    /**
     * 条件 columnName=value
     *
     * @param columnName
     * @param value
     * @return
     */
    public Sql eqDateTime(String columnName, Date value) {
        return eqDateTime(columnName, value, true);
    }

    /**
     * 条件 columnName=value
     *
     * @param columnName
     * @param value
     * @param ignoreIfValueIsNull
     * @return
     */
    public Sql eqDateTime(String columnName, Date value, boolean ignoreIfValueIsNull) {
        if (value == null) {
            if (ignoreIfValueIsNull) {
                return this;
            }
            throw new RuntimeException("sql eqDateTime 'value' is null");
        }
        processEq(columnName, JBoltDateUtil.format(value, JBoltDateUtil.YMDHMS));
        return this;
    }

    /**
     * 条件 columnName=innerClumnName
     *
     * @param columnName
     * @param innerColumnName
     * @return
     */
    public Sql eqInnerColumn(String columnName, String innerColumnName) {
        return eqInnerColumn(columnName, innerColumnName, true);
    }

    /**
     * 条件 columnName=innerClumnName
     *
     * @param columnName
     * @param innerColumnName
     * @param ignoreIfValueIsNull
     * @return
     */
    public Sql eqInnerColumn(String columnName, String innerColumnName, boolean ignoreIfValueIsNull) {
        if (StrKit.hasBlank(columnName, innerColumnName)) {
            throw new RuntimeException("Sql.eqInnerColumn(p1,p2) 两个参数必填");
        }
        return eq(columnName, new SqlExpress(innerColumnName), ignoreIfValueIsNull);
    }

    /**
     * 条件 having [sqlExpressStr]
     *
     * @param sqlExpressStr 表达式字符串
     * @return
     */
    public Sql having(String sqlExpressStr) {
        if (StrKit.isBlank(sqlExpressStr)) {
            throw new RuntimeException("Sql.having(para) 参数必填");
        }
        this.havingSqlExpress = new SqlExpress(sqlExpressStr);
        return this;
    }

    /**
     * 条件 sqlExpressStr=value
     *
     * @param sqlExpressStr 表达式字符串
     * @param value
     * @return
     */
    public Sql sqlExpressEq(String sqlExpressStr, Object value) {
        return sqlExpressEq(sqlExpressStr, value, true);
    }

    /**
     * 条件 sqlExpressStr=value
     *
     * @param sqlExpressStr       表达式字符串
     * @param value
     * @param ignoreIfValueIsNull
     * @return
     */
    public Sql sqlExpressEq(String sqlExpressStr, Object value, boolean ignoreIfValueIsNull) {
        if (StrKit.isBlank(sqlExpressStr) || value == null) {
            throw new RuntimeException("Sql.sqlExpressEq(p1,p2) 两个参数必填");
        }
        processAnd();
        conditions.add(new Condition(sqlExpressStr, value, Condition.SQL_EXPRESS_EQ));
        return this;
    }

    /**
     * 条件 sqlExpressStr!=value
     *
     * @param sqlExpressStr 表达式字符串
     * @param value
     * @return
     */
    public Sql sqlExpressNotEq(String sqlExpressStr, Object value) {
        return sqlExpressNotEq(sqlExpressStr, value, true);
    }

    /**
     * 条件 sqlExpressStr!=value
     *
     * @param sqlExpressStr       表达式字符串
     * @param value
     * @param ignoreIfValueIsNull
     * @return
     */
    public Sql sqlExpressNotEq(String sqlExpressStr, Object value, boolean ignoreIfValueIsNull) {
        if (StrKit.isBlank(sqlExpressStr) || value == null) {
            throw new RuntimeException("Sql.sqlExpressNotEq(p1,p2) 两个参数必填");
        }
        processAnd();
        conditions.add(new Condition(sqlExpressStr, value, Condition.SQL_EXPRESS_NOT_EQ));
        return this;
    }

    /**
     * 条件 sqlExpressStr>value
     *
     * @param sqlExpressStr 表达式字符串
     * @param value
     * @return
     */
    public Sql sqlExpressGt(String sqlExpressStr, Object value) {
        return sqlExpressGt(sqlExpressStr, value, true);
    }

    /**
     * 条件 sqlExpressStr>value
     *
     * @param sqlExpressStr       表达式字符串
     * @param value
     * @param ignoreIfValueIsNull
     * @return
     */
    public Sql sqlExpressGt(String sqlExpressStr, Object value, boolean ignoreIfValueIsNull) {
        if (StrKit.isBlank(sqlExpressStr) || value == null) {
            throw new RuntimeException("Sql.sqlExpressGt(p1,p2) 两个参数必填");
        }
        processAnd();
        conditions.add(new Condition(sqlExpressStr, value, Condition.SQL_EXPRESS_GT));
        return this;
    }

    /**
     * 条件 sqlExpressStr<value
     *
     * @param sqlExpressStr 表达式字符串
     * @param value
     * @return
     */
    public Sql sqlExpressLt(String sqlExpressStr, Object value) {
        return sqlExpressLt(sqlExpressStr, value, true);
    }

    /**
     * 条件 sqlExpressStr<value
     *
     * @param sqlExpressStr       表达式字符串
     * @param value
     * @param ignoreIfValueIsNull
     * @return
     */
    public Sql sqlExpressLt(String sqlExpressStr, Object value, boolean ignoreIfValueIsNull) {
        if (StrKit.isBlank(sqlExpressStr) || value == null) {
            throw new RuntimeException("Sql.sqlExpressLt(p1,p2) 两个参数必填");
        }
        processAnd();
        conditions.add(new Condition(sqlExpressStr, value, Condition.SQL_EXPRESS_LT));
        return this;
    }


    /**
     * 条件 sqlExpressStr>=value
     *
     * @param sqlExpressStr 表达式字符串
     * @param value
     * @return
     */
    public Sql sqlExpressGe(String sqlExpressStr, Object value) {
        return sqlExpressGe(sqlExpressStr, value, true);
    }

    /**
     * 条件 sqlExpressStr>=value
     *
     * @param sqlExpressStr       表达式字符串
     * @param value
     * @param ignoreIfValueIsNull
     * @return
     */
    public Sql sqlExpressGe(String sqlExpressStr, Object value, boolean ignoreIfValueIsNull) {
        if (StrKit.isBlank(sqlExpressStr) || value == null) {
            throw new RuntimeException("Sql.sqlExpressGe(p1,p2) 两个参数必填");
        }
        processAnd();
        conditions.add(new Condition(sqlExpressStr, value, Condition.SQL_EXPRESS_GE));
        return this;
    }


    /**
     * 条件 sqlExpressStr<=value
     *
     * @param sqlExpressStr 表达式字符串
     * @param value
     * @return
     */
    public Sql sqlExpressLe(String sqlExpressStr, Object value) {
        return sqlExpressLe(sqlExpressStr, value, true);
    }

    /**
     * 条件 sqlExpressStr<=value
     *
     * @param sqlExpressStr       表达式字符串
     * @param value
     * @param ignoreIfValueIsNull
     * @return
     */
    public Sql sqlExpressLe(String sqlExpressStr, Object value, boolean ignoreIfValueIsNull) {
        if (StrKit.isBlank(sqlExpressStr) || value == null) {
            throw new RuntimeException("Sql.sqlExpressLe(p1,p2) 两个参数必填");
        }
        processAnd();
        conditions.add(new Condition(sqlExpressStr, value, Condition.SQL_EXPRESS_LE));
        return this;
    }


    /**
     * 条件 columnName=sqlExpressStr
     *
     * @param columnName
     * @param sqlExpressStr 表达式字符串
     * @return
     */
    public Sql eqSqlExpress(String columnName, String sqlExpressStr) {
        return eqSqlExpress(columnName, sqlExpressStr, true);
    }

    /**
     * 条件 columnName=sqlExpressStr
     *
     * @param columnName
     * @param sqlExpressStr       表达式字符串
     * @param ignoreIfValueIsNull
     * @return
     */
    public Sql eqSqlExpress(String columnName, String sqlExpressStr, boolean ignoreIfValueIsNull) {
        if (StrKit.hasBlank(columnName, sqlExpressStr)) {
            throw new RuntimeException("Sql.eqSqlExpress(p1,p2) 两个参数必填");
        }
        return eq(columnName, new SqlExpress(sqlExpressStr), ignoreIfValueIsNull);
    }

    /**
     * 条件 columnName>=sqlExpressStr
     *
     * @param columnName
     * @param sqlExpressStr 表达式字符串
     * @return
     */
    public Sql geSqlExpress(String columnName, String sqlExpressStr) {
        return geSqlExpress(columnName, sqlExpressStr, true);
    }

    /**
     * 条件 columnName>=sqlExpressStr
     *
     * @param columnName
     * @param sqlExpressStr       表达式字符串
     * @param ignoreIfValueIsNull
     * @return
     */
    public Sql geSqlExpress(String columnName, String sqlExpressStr, boolean ignoreIfValueIsNull) {
        if (StrKit.hasBlank(columnName, sqlExpressStr)) {
            throw new RuntimeException("Sql.geSqlExpress(p1,p2) 两个参数必填");
        }
        return ge(columnName, new SqlExpress(sqlExpressStr), ignoreIfValueIsNull);
    }

    /**
     * 条件 columnName>sqlExpressStr
     *
     * @param columnName
     * @param sqlExpressStr 表达式字符串
     * @return
     */
    public Sql gtSqlExpress(String columnName, String sqlExpressStr) {
        return gtSqlExpress(columnName, sqlExpressStr, true);
    }

    /**
     * 条件 columnName>sqlExpressStr
     *
     * @param columnName
     * @param sqlExpressStr       表达式字符串
     * @param ignoreIfValueIsNull
     * @return
     */
    public Sql gtSqlExpress(String columnName, String sqlExpressStr, boolean ignoreIfValueIsNull) {
        if (StrKit.hasBlank(columnName, sqlExpressStr)) {
            throw new RuntimeException("Sql.gtSqlExpress(p1,p2) 两个参数必填");
        }
        return gt(columnName, new SqlExpress(sqlExpressStr), ignoreIfValueIsNull);
    }

    /**
     * 条件 columnName<sqlExpressStr
     *
     * @param columnName
     * @param sqlExpressStr 表达式字符串
     * @return
     */
    public Sql ltSqlExpress(String columnName, String sqlExpressStr) {
        return ltSqlExpress(columnName, sqlExpressStr, true);
    }

    /**
     * 条件 columnName<sqlExpressStr
     *
     * @param columnName
     * @param sqlExpressStr       表达式字符串
     * @param ignoreIfValueIsNull
     * @return
     */
    public Sql ltSqlExpress(String columnName, String sqlExpressStr, boolean ignoreIfValueIsNull) {
        if (StrKit.hasBlank(columnName, sqlExpressStr)) {
            throw new RuntimeException("Sql.ltSqlExpress(p1,p2) 两个参数必填");
        }
        return lt(columnName, new SqlExpress(sqlExpressStr), ignoreIfValueIsNull);
    }

    /**
     * 条件 columnName<=sqlExpressStr
     *
     * @param columnName
     * @param sqlExpressStr 表达式字符串
     * @return
     */
    public Sql leSqlExpress(String columnName, String sqlExpressStr) {
        return leSqlExpress(columnName, sqlExpressStr, true);
    }

    /**
     * 条件 columnName<=sqlExpressStr
     *
     * @param columnName
     * @param sqlExpressStr       表达式字符串
     * @param ignoreIfValueIsNull
     * @return
     */
    public Sql leSqlExpress(String columnName, String sqlExpressStr, boolean ignoreIfValueIsNull) {
        if (StrKit.hasBlank(columnName, sqlExpressStr)) {
            throw new RuntimeException("Sql.leSqlExpress(p1,p2) 两个参数必填");
        }
        return le(columnName, new SqlExpress(sqlExpressStr), ignoreIfValueIsNull);
    }

    /**
     * Boolean转char(1) eq等于
     *
     * @param columnName
     * @param value
     * @return
     */
    public Sql eqBooleanToChar(String columnName, Boolean value) {
        return eqBooleanToChar(columnName, value, true);
    }

    /**
     * Boolean转char(1) eq等于
     *
     * @param columnName
     * @param value
     * @return
     */
    public Sql eqBooleanToChar(String columnName, Boolean value, boolean ignoreIfValueIsNull) {
        if (StrKit.isBlank(columnName)) {
            throw new RuntimeException("sql eqBooleanToChar 'columnName' is null");
        }
        if (value == null) {
            if (ignoreIfValueIsNull) {
                return this;
            }
            throw new RuntimeException("sql eqBooleanToChar 'value' is null");
        }
        processEq(columnName, value ? TRUE : FALSE);
        return this;
    }

    /**
     * Boolean转char(1) not eq 不等于
     *
     * @param columnName
     * @param value
     * @return
     */
    public Sql notEqBooleanToChar(String columnName, Boolean value) {
        return notEqBooleanToChar(columnName, value, true);
    }

    /**
     * Boolean转char(1) not eq 不等于
     *
     * @param columnName
     * @param value
     * @param ignoreIfValueIsNull
     * @return
     */
    public Sql notEqBooleanToChar(String columnName, Boolean value, boolean ignoreIfValueIsNull) {
        if (StrKit.isBlank(columnName)) {
            throw new RuntimeException("sql notEqBooleanToChar 'columnName' is null");
        }
        if (value == null) {
            if (ignoreIfValueIsNull) {
                return this;
            }
            throw new RuntimeException("sql notEqBooleanToChar 'value' is null");
        }
        processAnd();
        conditions.add(new Condition(processPrependMainTableAsName(columnName), value ? TRUE : FALSE, Condition.NOT_EQ));
        return this;
    }

    /**
     * 条件 columnName=value
     * int数据不能小于等于0
     *
     * @param columnName
     * @param value
     * @return
     */
    public Sql eqIfOk(String columnName, Integer value) {
        if (StrKit.isBlank(columnName)) {
            throw new RuntimeException("sql eqIfOk 'columnName' is null");
        }
        if (value == null || value <= 0) {
            return this;
        }
        processEq(columnName, value);
        return this;
    }

    /**
     * 条件 columnName=value
     * long数据不能小于等于0
     *
     * @param columnName
     * @param value
     * @return
     */
    public Sql eqIfOk(String columnName, Long value) {
        if (StrKit.isBlank(columnName)) {
            throw new RuntimeException("sql eqIfOk 'columnName' is null");
        }
        if (value == null || value.longValue() <= 0) {
            return this;
        }
        processEq(columnName, value);
        return this;
    }

    /**
     * 条件 columnName=value
     * BigDecimal数据不能小于等于0
     *
     * @param columnName
     * @param value
     * @return
     */
    public Sql eqIfOk(String columnName, BigDecimal value) {
        if (StrKit.isBlank(columnName)) {
            throw new RuntimeException("sql eqIfOk 'columnName' is null");
        }
        if (value == null || value.doubleValue() <= 0) {
            return this;
        }
        processEq(columnName, value);
        return this;
    }

    /**
     * 底层处理eq
     *
     * @param columnName
     * @param value
     */
    private void processEq(String columnName, Object value) {
        processAnd();
        conditions.add(new Condition(processPrependMainTableAsName(columnName), value, Condition.EQ));
    }

    /**
     * 条件 columnName=value
     * String数据不能为null或者""空字符串
     *
     * @param columnName
     * @param value
     * @return
     */
    public Sql eqIfNotBlank(String columnName, String value) {
        if (StrKit.isBlank(columnName)) {
            throw new RuntimeException("sql eqIfNotBlank 'columnName' is null");
        }
        if (StrKit.isBlank(value)) {
            return this;
        }
        processEq(columnName, value);
        return this;
    }

    /**
     * 条件(columnName=value1 or  columnName=value2)
     *
     * @param columnName
     * @param values
     * @return
     */
    public Sql eqOr(String columnName, Object... values) {
        if (StrKit.isBlank(columnName) || values == null || values.length == 0) {
            return this;
        }
        processAnd();
        bracketLeft();
        int len = values.length;
        for (int i = 0; i < len; i++) {
            conditions.add(new Condition(processPrependMainTableAsName(columnName), values[i], Condition.EQ));
            if (i < len - 1) {
                conditions.add(new Condition().or());
            }
        }

        bracketRight();
        return this;
    }

    /**
     * 条件 id=value
     *
     * @param value
     * @return
     */
    public Sql idEq(Object value) {
        return eq(KEY_ID, value, false);
    }

    /**
     * 条件 pid=value
     *
     * @param value
     * @return
     */
    public Sql pidEq(Object value) {
        return eq(KEY_PID, value, false);
    }

    /**
     * 条件 key=? 可批量处理
     *
     * @param keys
     * @return
     */
    public Sql eqQM(String... keys) {
        if (keys != null && keys.length > 0) {
            for (String columnName : keys) {
                eq(columnName, KEY_QUESTION_MARK);
            }
        }
        return this;
    }

    /**
     * 条件 id=?
     *
     * @return
     */
    public Sql idEqQM() {
        return eqQM(KEY_ID);
    }

    /**
     * 条件 pid=?
     *
     * @return
     */
    public Sql pidEqQM() {
        return eqQM(KEY_PID);
    }

    /**
     * 条件 id=?
     * 指定id名字
     *
     * @param idName
     * @return
     */
    public Sql idEqQM(String idName) {
        return eqQM(idName);
    }

    /**
     * 条件 pid=?
     * 指定pid名字
     *
     * @param pidName
     * @return
     */
    public Sql pidEqQM(String pidName) {
        return eqQM(pidName);
    }

    /**
     * 条件 key like value
     * value 默认不 trim
     *
     * @param columnName
     * @param value
     * @return
     */
    public Sql like(String columnName, String value) {
        return like(columnName, value, false);
    }

    /**
     * 条件 key left like value 左匹配
     *
     * @param columnName
     * @param value
     * @return
     */
    public Sql leftLike(String columnName, String value) {
        return startWith(columnName, value);
    }

    /**
     * 条件 key left like value 左匹配
     *
     * @param columnName
     * @param value
     * @return
     */
    public Sql rightLike(String columnName, String value) {
        return endWith(columnName, value);
    }

    /**
     * 条件 key like value
     * 自定义是否value trim
     *
     * @param columnName
     * @param value
     * @param trim
     * @return
     */
    public Sql like(String columnName, String value, boolean trim) {
        if (StrKit.isBlank(columnName) || value == null) {
            return this;
        }
        if (trim) {
            value = value.trim();
        }
        if (value.length() == 0) {
            return this;
        }
        if (columnName.contains(KEY_COMMA) && !columnName.contains("(") && !columnName.contains(")") && !columnName.contains("||")) {
            return likeMulti(value, columnName);
        }
        processAnd();
        conditions.add(new Condition(processPrependMainTableAsName(columnName), value, Condition.LIKE));
        return this;
    }

    /**
     * 多字段like
     * 默认value 不trim
     *
     * @param likeValue
     * @param columns
     * @return
     */
    public Sql likeMulti(String likeValue, String... columns) {
        return likeMulti(likeValue, false, columns);
    }

    /**
     * 多字段like
     * 自定义 value trim
     *
     * @param likeValue
     * @param trim
     * @param columns
     * @return
     */
    public Sql likeMulti(String likeValue, boolean trim, String... columns) {
        if (columns == null || columns.length == 0 || likeValue == null) {
            return this;
        }
        if (trim) {
            likeValue = likeValue.trim();
        }
        if (likeValue.length() == 0) {
            return this;
        }
        int len = columns.length;
        if (len == 1) {
            String first = columns[0];
            if (StrKit.isBlank(first)) {
                return this;
            }

            if (first.contains(KEY_COMMA)) {
                columns = JBoltArrayUtil.from3(columns[0]);
                len = columns.length;
            }
        }

        bracketLeft();
        for (int i = 0; i < len; i++) {
            like(columns[i], likeValue);
            if (i != len - 1) {
                or();
            }
        }
        bracketRight();
        return this;
    }

    /**
     * 多字段 not like
     *
     * @param likeValue
     * @param columns
     * @return
     */
    public Sql notLikeMulti(String likeValue, String... columns) {
        if (StrKit.notBlank(likeValue) && columns != null && columns.length > 0) {
            int len = columns.length;
            if (len == 1) {
                String first = columns[0];
                if (StrKit.isBlank(first)) {
                    return this;
                }

                if (first.contains(KEY_COMMA)) {
                    columns = JBoltArrayUtil.from3(columns[0]);
                    len = columns.length;
                }
            }

            bracketLeft();
            for (int i = 0; i < len; i++) {
                notLike(columns[i], likeValue);
                if (i != len - 1) {
                    and();
                }
            }
            bracketRight();
        }
        return this;
    }

    /**
     * 条件 key not like value
     *
     * @param columnName
     * @param value
     * @return
     */
    public Sql notLike(String columnName, String value) {
        if (StrKit.isBlank(columnName) || StrKit.isBlank(value)) {
            return this;
        }
        if (columnName.contains(KEY_COMMA)) {
            return notLikeMulti(value, columnName);
        }
        processAnd();
        conditions.add(new Condition(processPrependMainTableAsName(columnName), value, Condition.NOTLIKE));
        return this;
    }

    /**
     * 条件 value以key开头模糊查询
     *
     * @param columnName
     * @param value
     * @return
     */
    public Sql startWith(String columnName, String value) {
        if (StrKit.isBlank(columnName) || StrKit.isBlank(value)) {
            return this;
        }
        processAnd();
        conditions.add(new Condition(processPrependMainTableAsName(columnName), value, Condition.STARTWITH));
        return this;
    }

    /**
     * 条件 value不以key开头模糊查询
     *
     * @param columnName
     * @param value
     * @return
     */
    public Sql notStartWith(String columnName, String value) {
        if (StrKit.isBlank(columnName) || StrKit.isBlank(value)) {
            return this;
        }
        processAnd();
        conditions.add(new Condition(processPrependMainTableAsName(columnName), value, Condition.NOT_STARTWITH));
        return this;
    }

    /**
     * 条件 value以key结尾模糊查询
     *
     * @param columnName
     * @param value
     * @return
     */
    public Sql endWith(String columnName, String value) {
        if (StrKit.isBlank(columnName) || StrKit.isBlank(value)) {
            return this;
        }
        processAnd();
        conditions.add(new Condition(processPrependMainTableAsName(columnName), value, Condition.ENDWITH));
        return this;
    }

    /**
     * 条件 value不以key结尾模糊查询
     *
     * @param columnName
     * @param value
     * @return
     */
    public Sql notEndWith(String columnName, String value) {
        if (StrKit.isBlank(columnName) || StrKit.isBlank(value)) {
            return this;
        }
        processAnd();
        conditions.add(new Condition(processPrependMainTableAsName(columnName), value, Condition.NOT_ENDWITH));
        return this;
    }

    /**
     * 分组查询
     * 默认不处理自动加上主表as的别名
     *
     * @param columnNames
     * @return
     */
    public Sql groupBys(String... columnNames) {
        return groupBys(false, columnNames);
    }

    /**
     * 分组查询
     *
     * @param processPrependMainTableAsName 是否处理每个groupBy的列 都增加主表as的别名
     * @param columnNames
     * @return
     */
    public Sql groupBys(boolean processPrependMainTableAsName, String... columnNames) {
        if (columnNames == null || columnNames.length == 0) {
            throw new RuntimeException("group by ColumnNames is null");
        }
        if (processPrependMainTableAsName) {
            int len = columnNames.length;
            for (int i = 0; i < len; i++) {
                columnNames[i] = processPrependMainTableAsName(columnNames[i]);
            }
        }
        this.groupBys = columnNames;
        return this;
    }

    /**
     * 单个 groupBy
     *
     * @param columnName
     * @return
     */
    public Sql groupBy(String columnName) {
        return groupBy(columnName, false);
    }

    /**
     * 单个 groupBy
     *
     * @param columnName
     * @param processPrependMainTableAsName 是否处理每个groupBy的列 都增加主表as的别名
     * @return
     */
    public Sql groupBy(String columnName, boolean processPrependMainTableAsName) {
        if (columnName != null && columnName.trim().length() > 0 && columnName.contains(",")) {
            return groupBys(processPrependMainTableAsName, JBoltArrayUtil.from(columnName));
        }
        return groupBys(processPrependMainTableAsName, columnName);
    }

    /**
     * 是否为groupBy查询
     *
     * @return
     */
    public boolean isGroupBy() {
        return this.groupBys != null && this.groupBys.length > 0;
    }

    /**
     * 是否为groupBy查询
     *
     * @return
     */
    public boolean isHaving() {
        return this.havingSqlExpress != null;
    }

    /**
     * 是否为groupBy查询
     *
     * @return
     */
    public boolean isGroupByAndQueryCount() {
        return isGroupBy() && (isQueryCount() || isQueryDistinctCount());
    }

    /**
     * 排序查询
     *
     * @param columnName
     * @param orderType
     * @return
     */
    public Sql orderBy(String columnName, String orderType) {
        return orderBy(columnName, KEY_DESC_TRIMED.equalsIgnoreCase(orderType));
    }

    /**
     * 排序查询
     *
     * @param columnName
     * @param orderType
     * @param processPrependMainTableAsName
     * @return
     */
    public Sql orderBy(String columnName, String orderType, boolean processPrependMainTableAsName) {
        return orderBy(columnName, KEY_DESC_TRIMED.equalsIgnoreCase(orderType), processPrependMainTableAsName);
    }

    /**
     * 排序查询 直接传自定义sql
     *
     * @param orderBySql
     * @return
     */
    public Sql orderBySql(String orderBySql) {
        if (StrKit.isBlank(orderBySql)) {
            return this;
        }
        ;
        String[] ods = JBoltArrayUtil.from3(orderBySql);
        if (ods.length == 0) {
            LogKit.warn("orderBySql:" + orderBySql + " is Empty!");
            return this;
        }
        String[] oar;
        for (String od : ods) {
            oar = JBoltArrayUtil.from(od, " ");
            if (oar.length == 1) {
                asc(oar[0]);
            } else if (oar.length == 2) {
                orderBy(oar[0], oar[1]);
            }
        }
        return this;
    }

    /**
     * 排序查询
     *
     * @param columnName
     * @param desc
     * @return
     */
    public Sql orderBy(String columnName, boolean desc) {
        return orderBy(columnName, desc, true);
    }

    /**
     * 排序查询
     *
     * @param columnName
     * @param desc
     * @param processPrependMainTableAsName 是否处理增加主表别名
     * @return
     */
    public Sql orderBy(String columnName, boolean desc, boolean processPrependMainTableAsName) {
        if (StrKit.isBlank(columnName)) {
            throw new RuntimeException("Sql中order by的columnName不能为空");
        }
        initOrderBys();
        String column = processPrependMainTableAsName ? processPrependMainTableAsName(columnName) : columnName;
        this.orderBys.add(OrderBy.create(column, desc));
        return this;
    }

    /**
     * 倒序查询
     * 默认不处理增加主表别名
     *
     * @param desc
     * @param columnNames
     * @return
     */
    public Sql orderBy(boolean desc, String... columnNames) {
        return orderBy(desc, isDM(), columnNames);
    }

    /**
     * 倒序查询
     *
     * @param desc
     * @param processPrependMainTableAsName 是否处理增加主表别名
     * @param columnNames
     * @return
     */
    public Sql orderBy(boolean desc, boolean processPrependMainTableAsName, String... columnNames) {
        if (columnNames == null || columnNames.length == 0) {
            throw new RuntimeException("Sql中orderBy(desc,columnNames)的columnNames不能为空");
        }
        for (String columnName : columnNames) {
            orderBy(columnName, desc, processPrependMainTableAsName);
        }
        return this;
    }


    /**
     * 初始化orderBys
     */
    private void initOrderBys() {
        if (this.orderBys == null) {
            this.orderBys = new ArrayList<OrderBy>();
        }
    }

    /**
     * 清掉order by
     */
    public Sql clearOrderBy() {
        this.orderBys = null;
        return this;
    }

    /**
     * 清掉group by
     */
    public Sql clearGroupBy() {
        this.groupBys = null;
        return this;
    }


    /**
     * 排序
     *
     * @param orderBys 可变数组参数
     * @return
     */
    public Sql orderBy(OrderBy... orderBys) {
        if (orderBys == null || orderBys.length == 0) {
            throw new RuntimeException("Sql.java中orderBy(orderBys)的orderBys不能为空");
        }
        initOrderBys();
        this.orderBys.addAll(Arrays.asList(orderBys));
        return this;
    }

    /**
     * 正排序
     *
     * @param columnNames 可变数组参数
     * @return
     */
    public Sql asc(String... columnNames) {
        return orderBy(false, isDM(), columnNames);
    }

    /**
     * 正排序
     *
     * @param processPrependMainTableAsName 是否处理增加主表别名
     * @param columnNames                   可变数组参数
     * @return
     */
    public Sql asc(boolean processPrependMainTableAsName, String... columnNames) {
        return orderBy(false, processPrependMainTableAsName, columnNames);
    }

    /**
     * 倒排序
     *
     * @param columnNames 可变数组参数
     * @return
     */
    public Sql desc(String... columnNames) {
        return orderBy(true, isDM(), columnNames);
    }

    /**
     * 倒排序
     *
     * @param processPrependMainTableAsName 是否处理增加主表别名
     * @param columnNames                   可变数组参数
     * @return
     */
    public Sql desc(boolean processPrependMainTableAsName, String... columnNames) {
        return orderBy(true, processPrependMainTableAsName, columnNames);
    }

    /**
     * 按照ID排序 默认正序
     *
     * @return
     */
    public Sql orderById() {
        return orderById(false);
    }

    /**
     * 按照ID排序 指定是否倒序 是否处理别名
     *
     * @param desc
     * @param processPrependMainTableAsName
     * @return
     */
    public Sql orderById(boolean desc, boolean processPrependMainTableAsName) {
        return orderBy(KEY_ID, desc, processPrependMainTableAsName);
    }

    /**
     * 按照ID排序 指定是否倒序
     *
     * @return
     */
    public Sql orderById(boolean desc) {
        return orderBy(KEY_ID, desc);
    }

    /**
     * 按照sort_rank字段排序 正序
     *
     * @param processPrependMainTableAsName
     * @return
     */
    public Sql orderBySortRank(boolean processPrependMainTableAsName) {
        return asc(processPrependMainTableAsName, KEY_SORT_RANK);
    }

    /**
     * 按照sort_rank字段排序 正序
     *
     * @return
     */
    public Sql orderBySortRank() {
        return asc(KEY_SORT_RANK);
    }

    /**
     * 按照sort_rank字段排序 正序
     *
     * @param sorkRankColumnName            排序列名
     * @param processPrependMainTableAsName
     * @return
     */
    public Sql orderBySortRank(String sorkRankColumnName, boolean processPrependMainTableAsName) {
        return asc(processPrependMainTableAsName, sorkRankColumnName);
    }

    /**
     * 按照sort_rank字段排序 正序
     *
     * @param sorkRankColumnName 排序列名
     * @return
     */
    public Sql orderBySortRank(String sorkRankColumnName) {
        return asc(sorkRankColumnName);
    }

    /**
     * 处理条件前加and
     */
    private void processAnd() {
        if (conditions.size() > 0) {
            Condition condition = conditions.get(conditions.size() - 1);
            if (condition.getType() != Condition.TYPE_LINK || (condition.getType() == Condition.TYPE_LINK && condition.getValue1().equals(Condition.BRACKET_RIGHT))) {
                and();
            }
        }
    }

    /**
     * 底层处理joinSqlWithTable 添加到joinSqls里
     *
     * @param dir
     * @param joinSqlWithTable
     */
    private void addJoinSql(String dir, String joinSqlWithTable) {
        if (joinSqlWithTable == null || joinSqlWithTable.length() == 0) {
            return;
        }
        if (this.joinSqls == null) {
            this.joinSqls = new ArrayList<String>();
        }
        this.joinSqls.add(dir + joinSqlWithTable + KEY_WHITESPACE);
    }


    /**
     * 关联查询 左连接
     *
     * @param joinSql
     * @return
     */
    public Sql leftJoin(String joinSql) {
        if (joinSql != null && joinSql.length() > 0) {
            addJoinSql(KEY_LEFT_JOIN, joinSql);
        }
        return this;
    }

    /**
     * 关联查询 右连接
     *
     * @param joinSql
     * @return
     */
    public Sql rightJoin(String joinSql) {
        if (joinSql != null && joinSql.length() > 0) {
            addJoinSql(KEY_RIGHT_JOIN, joinSql);
        }
        return this;
    }

    /**
     * 关联查询 内连接
     *
     * @param joinSql
     * @return
     */
    public Sql innerJoin(String joinSql) {
        if (joinSql != null && joinSql.length() > 0) {
            addJoinSql(KEY_INNER_JOIN, joinSql);
        }
        return this;
    }

    /**
     * 关联查询 左外连接
     *
     * @param joinSql
     * @return
     */
    public Sql leftOuterJoin(String joinSql) {
        if (joinSql != null && joinSql.length() > 0) {
            addJoinSql(KEY_LEFT_OUTER_JOIN, joinSql);
        }
        return this;
    }

    /**
     * 关联查询 右外连接
     *
     * @param joinSql
     * @return
     */
    public Sql rightOuterJoin(String joinSql) {
        if (joinSql != null && joinSql.length() > 0) {
            addJoinSql(KEY_RIGHT_OUTER_JOIN, joinSql);
        }
        return this;
    }

    /**
     * 关联查询 全连接
     *
     * @param joinSql
     * @return
     */
    public Sql fullJoin(String joinSql) {
        if (joinSql != null && joinSql.length() > 0) {
            addJoinSql(KEY_FULL_JOIN, joinSql);
        }
        return this;
    }

    /**
     * 关联查询 左连接
     *
     * @param joinTableName
     * @param asName
     * @param onSql
     * @return
     */
    public Sql leftJoin(String joinTableName, String asName, String onSql) {
        if (isOracle()) {
            addJoinSql(KEY_LEFT_JOIN, processTableNameByDbType(joinTableName) + KEY_WHITESPACE + asName + KEY_ON + onSql);
        } else if (isDM()) {
            addJoinSql(KEY_LEFT_JOIN, processTableNameByDbType(joinTableName) + KEY_AS + asName + KEY_ON + processDmOnsql(onSql));
        } else {
            addJoinSql(KEY_LEFT_JOIN, processTableNameByDbType(joinTableName) + KEY_AS + asName + KEY_ON + onSql);
        }
        return this;
    }

    /**
     * 处理特殊onsql
     *
     * @param sql
     * @return
     */
    private String processDmOnsql(String sql) {
        if (sql.contains(KEY_QUOTATION_MARKS) || sql.contains(KEY_AT)) {
            return sql;
        }

        // xxx=yyy and zzz=aaa
        if (sql.contains(KEY_AND) && sql.contains(KEY_EQUALS_TRIMED) && !sql.contains(KEY_BETWEEN)) {
            String[] arr = JBoltArrayUtil.from(sql, KEY_AND);
            int size = arr.length;
            for (int i = 0; i < size; i++) {
                arr[i] = processDmOnsql(arr[i]);
            }
            return JBoltArrayUtil.join(arr, KEY_AND);
        }

        // xxx=yyy
        if (sql.contains(KEY_EQUALS_TRIMED)) {
            String[] arr = JBoltArrayUtil.from(sql, KEY_EQUALS_TRIMED);
            int size = arr.length;
            for (int i = 0; i < size; i++) {
                arr[i] = processDmOnsql(arr[i]);
            }
            return JBoltArrayUtil.join(arr, KEY_EQUALS_TRIMED);
        }

        // name between xxx and yyy
        if (sql.contains(KEY_BETWEEN) && sql.contains(KEY_AND)) {
            String[] arr = new String[3];
            arr[0] = processDmOnsql(sql.substring(0, sql.indexOf(KEY_BETWEEN_LEFT)).trim());
            arr[1] = processDmOnsql(sql.substring(sql.indexOf(KEY_BETWEEN_RIGHT) + 8, sql.indexOf(KEY_AND_LEFT)).trim());
            arr[2] = processDmOnsql(sql.substring(sql.indexOf(KEY_AND_RIGHT) + 4).trim());
            return arr[0] + KEY_BETWEEN + arr[1] + KEY_AND + arr[2];
        }

        // xxx.yyy
        if (sql.contains(KEY_DOT)) {
            String[] arr = JBoltArrayUtil.from(sql, KEY_DOT_WITH_SPLASH);
            arr[1] = processNameIfDM(arr[1]);
            return JBoltArrayUtil.join(arr, KEY_DOT);
        }

        return processNameIfDM(sql);

    }

    /**
     * 关联查询 左连接
     *
     * @param joinTableName   leftjoin的表
     * @param asName          别名
     * @param thisTableColumn 主表持有关联列 （逻辑外键名）
     * @param joinTableColumn 关联表中的对应列名（一般是ID）
     */
    public Sql leftJoin(String joinTableName, String asName, String thisTableColumn, String joinTableColumn) {
        return leftJoin(joinTableName, asName, processOnSql(asName, thisTableColumn, joinTableColumn));
    }

    /**
     * 关联查询 左连接 on条件是ID
     *
     * @param joinTableName   leftjoin的表
     * @param asName          别名
     * @param thisTableColumn 主表持有关联列 （逻辑外键名）
     */
    public Sql leftJoinOnId(String joinTableName, String asName, String thisTableColumn) {
        return leftJoin(joinTableName, asName, thisTableColumn, KEY_ID);
    }

    /**
     * 关联查询  左连接
     *
     * @param modelClass model类
     * @param asName     别名
     * @param onSql      leftjoin里 on 关联的sql
     * @return
     */
    @SuppressWarnings("rawtypes")
    public Sql leftJoin(Class<? extends Model> modelClass, String asName, String onSql) {
        return leftJoin(processTableName(modelClass), asName, onSql);
    }

    /**
     * 关联查询  左连接
     *
     * @param modelClass      model类
     * @param asName          别名
     * @param thisTableColumn 主表持有关联列 （逻辑外键名）
     * @param joinTableColumn 关联表中的对应列名（一般是ID）
     * @return
     */
    @SuppressWarnings("rawtypes")
    public Sql leftJoin(Class<? extends Model> modelClass, String asName, String thisTableColumn, String joinTableColumn) {
        return leftJoin(processTableName(modelClass), asName, thisTableColumn, joinTableColumn);
    }

    /**
     * 关联查询  左连接 on条件是ID
     *
     * @param modelClass      model类
     * @param asName          别名
     * @param thisTableColumn 主表持有关联列 （逻辑外键名）
     * @return
     */
    @SuppressWarnings("rawtypes")
    public Sql leftJoinOnId(Class<? extends Model> modelClass, String asName, String thisTableColumn) {
        return leftJoin(modelClass, asName, thisTableColumn, KEY_ID);
    }

    /**
     * 关联查询 右连接
     *
     * @param modelClass      model类
     * @param asName          别名
     * @param thisTableColumn 主表持有关联列 （逻辑外键名）
     * @param joinTableColumn 关联表中的对应列名（一般是ID）
     * @return
     */
    @SuppressWarnings("rawtypes")
    public Sql rightJoin(Class<? extends Model> modelClass, String asName, String thisTableColumn, String joinTableColumn) {
        return rightJoin(processTableName(modelClass), asName, thisTableColumn, joinTableColumn);
    }

    /**
     * 关联查询 右连接 on条件是ID
     *
     * @param modelClass      model类
     * @param asName          别名
     * @param thisTableColumn 主表持有关联列 （逻辑外键名）
     * @return
     */
    @SuppressWarnings("rawtypes")
    public Sql rightJoinOnId(Class<? extends Model> modelClass, String asName, String thisTableColumn) {
        return rightJoin(modelClass, asName, thisTableColumn, KEY_ID);
    }

    /**
     * 关联查询 右连接
     *
     * @param modelClass model类
     * @param asName     别名
     * @param onSql      on条件sql
     * @return
     */
    @SuppressWarnings("rawtypes")
    public Sql rightJoin(Class<? extends Model> modelClass, String asName, String onSql) {
        return rightJoin(processTableName(modelClass), asName, onSql);
    }

    /**
     * 关联查询 右连接
     *
     * @param joinTableName rightJoin的表
     * @param asName        别名
     * @param onSql         on条件sql
     * @return
     */
    public Sql rightJoin(String joinTableName, String asName, String onSql) {
        if (isOracle()) {
            addJoinSql(KEY_RIGHT_JOIN, processTableNameByDbType(joinTableName) + KEY_WHITESPACE + asName + KEY_ON + onSql);
        } else if (isDM()) {
            addJoinSql(KEY_RIGHT_JOIN, processTableNameByDbType(joinTableName) + KEY_AS + asName + KEY_ON + processDmOnsql(onSql));
        } else {
            addJoinSql(KEY_RIGHT_JOIN, processTableNameByDbType(joinTableName) + KEY_AS + asName + KEY_ON + onSql);
        }
        return this;
    }

    /**
     * 关联查询 右连接
     *
     * @param joinTableName   rightJoin表
     * @param asName          别名
     * @param thisTableColumn 主表持有关联列 （逻辑外键名）
     * @param joinTableColumn 关联表中的对应列名（一般是ID）
     */
    public Sql rightJoin(String joinTableName, String asName, String thisTableColumn, String joinTableColumn) {
        return rightJoin(joinTableName, asName, processOnSql(asName, thisTableColumn, joinTableColumn));
    }

    /**
     * 关联查询 右连接 on条件是id
     *
     * @param joinTableName   rightjoin表
     * @param asName          别名
     * @param thisTableColumn 主表持有关联列 （逻辑外键名）
     */
    public Sql rightJoinOnId(String joinTableName, String asName, String thisTableColumn) {
        return rightJoin(joinTableName, asName, thisTableColumn, KEY_ID);
    }

    /**
     * 连接其它Sql对象 去重
     *
     * @param sql
     * @return
     */
    public Sql union(Sql sql) {
        if (this.unionSqls == null) {
            this.unionSqls = new ArrayList<UnionSql>();
        }
        this.unionSqls.add(new UnionSql(sql, false));
        return this;
    }

    /**
     * 连接其它Sql对象 返回所有数据 包括重复
     *
     * @param sql
     * @return
     */
    public Sql unionAll(Sql sql) {
        if (this.unionSqls == null) {
            this.unionSqls = new ArrayList<UnionSql>();
        }
        this.unionSqls.add(new UnionSql(sql, true));
        return this;
    }

    /**
     * 检测是否包含union
     *
     * @return
     */
    public boolean hasUnion() {
        return unionSqls != null && unionSqls.size() > 0;
    }


    /**
     * 关联查询 内连接
     *
     * @param modelClass model类
     * @param asName     别名
     * @param onSql      on条件sql
     * @return
     */
    @SuppressWarnings("rawtypes")
    public Sql innerJoin(Class<? extends Model> modelClass, String asName, String onSql) {
        return innerJoin(processTableName(modelClass), asName, onSql);
    }

    /**
     * 关联查询 内连接
     *
     * @param modelClass      model类
     * @param asName          别名
     * @param thisTableColumn 主表持有关联列 （逻辑外键名）
     * @param joinTableColumn 关联表中的对应列名（一般是ID）
     * @return
     */
    @SuppressWarnings("rawtypes")
    public Sql innerJoin(Class<? extends Model> modelClass, String asName, String thisTableColumn, String joinTableColumn) {
        return innerJoin(processTableName(modelClass), asName, thisTableColumn, joinTableColumn);
    }

    /**
     * 关联查询 内连接 on条件是ID
     *
     * @param modelClass      model类
     * @param asName          别名
     * @param thisTableColumn 主表持有关联列 （逻辑外键名）
     * @return
     */
    @SuppressWarnings("rawtypes")
    public Sql innerJoinOnId(Class<? extends Model> modelClass, String asName, String thisTableColumn) {
        return innerJoin(modelClass, asName, thisTableColumn, KEY_ID);
    }

    /**
     * 关联查询 内连接
     *
     * @param joinTableName   innerJoin表
     * @param asName          别名
     * @param thisTableColumn 主表持有关联列 （逻辑外键名）
     * @param joinTableColumn 关联表中的对应列名（一般是ID）
     * @return
     */
    public Sql innerJoin(String joinTableName, String asName, String thisTableColumn, String joinTableColumn) {
        return innerJoin(joinTableName, asName, processOnSql(asName, thisTableColumn, joinTableColumn));
    }

    /**
     * 关联查询 内连接 on条件是ID
     *
     * @param joinTableName   innerJoin表
     * @param asName          别名
     * @param thisTableColumn 主表持有关联列 （逻辑外键名）
     * @return
     */
    public Sql innerJoinOnId(String joinTableName, String asName, String thisTableColumn) {
        return innerJoin(joinTableName, asName, thisTableColumn, KEY_ID);
    }

    /**
     * 处理关联查询onsql拼接
     *
     * @param asName          别名
     * @param thisTableColumn 主表持有关联列 （逻辑外键名）
     * @param joinTableColumn 关联表中的对应列名（一般是ID）
     * @return
     */
    private String processOnSql(String asName, String thisTableColumn, String joinTableColumn) {
        //是否包含asName+. 例如p.
        if (!joinTableColumn.startsWith(asName + KEY_DOT)) {
            joinTableColumn = asName + KEY_DOT + processColumnNameByDbType(joinTableColumn);
        }
        return processPrependMainTableAsName(thisTableColumn) + KEY_EQUALS_TRIMED + joinTableColumn;
    }

    /**
     * 关联查询 内连接
     *
     * @param joinTableName innerJoin表
     * @param asName        别名
     * @param onSql         on条件
     * @return
     */
    public Sql innerJoin(String joinTableName, String asName, String onSql) {
        if (isOracle()) {
            addJoinSql(KEY_INNER_JOIN, processTableNameByDbType(joinTableName) + KEY_WHITESPACE + asName + KEY_ON + onSql);
        } else if (isDM()) {
            addJoinSql(KEY_INNER_JOIN, processTableNameByDbType(joinTableName) + KEY_AS + asName + KEY_ON + processDmOnsql(onSql));
        } else {
            addJoinSql(KEY_INNER_JOIN, processTableNameByDbType(joinTableName) + KEY_AS + asName + KEY_ON + onSql);
        }
        return this;
    }

    /**
     * 关联查询 左外连接
     *
     * @param modelClass model类
     * @param asName     别名
     * @param onSql      on条件
     * @return
     */
    @SuppressWarnings("rawtypes")
    public Sql leftOuterJoin(Class<? extends Model> modelClass, String asName, String onSql) {
        return leftOuterJoin(processTableName(modelClass), asName, onSql);
    }

    /**
     * 关联查询 左外连接
     *
     * @param modelClass      model类
     * @param asName          别名
     * @param thisTableColumn 主表持有关联列 （逻辑外键名）
     * @param joinTableColumn 关联表中的对应列名（一般是ID）
     * @return
     */
    @SuppressWarnings("rawtypes")
    public Sql leftOuterJoin(Class<? extends Model> modelClass, String asName, String thisTableColumn, String joinTableColumn) {
        return leftOuterJoin(processTableName(modelClass), asName, thisTableColumn, joinTableColumn);
    }

    /**
     * 关联查询 左外连接  on条件是ID
     *
     * @param modelClass      model类
     * @param asName          别名
     * @param thisTableColumn 主表持有关联列 （逻辑外键名）
     * @return
     */
    @SuppressWarnings("rawtypes")
    public Sql leftOuterJoinOnId(Class<? extends Model> modelClass, String asName, String thisTableColumn) {
        return leftOuterJoin(modelClass, asName, thisTableColumn, KEY_ID);
    }

    /**
     * 关联查询 左外连接
     *
     * @param joinTableName   leftOuterJoin表
     * @param asName          别名
     * @param thisTableColumn 主表持有关联列 （逻辑外键名）
     * @param joinTableColumn 关联表中的对应列名（一般是ID）
     * @return
     */
    public Sql leftOuterJoin(String joinTableName, String asName, String thisTableColumn, String joinTableColumn) {
        return leftOuterJoin(joinTableName, asName, processOnSql(asName, thisTableColumn, joinTableColumn));
    }

    /**
     * 关联查询 左外连接 on条件是ID
     *
     * @param joinTableName   leftOuterJoin表
     * @param asName          别名
     * @param thisTableColumn 主表持有关联列 （逻辑外键名）
     * @return
     */
    public Sql leftOuterJoinOnId(String joinTableName, String asName, String thisTableColumn) {
        return leftOuterJoin(joinTableName, asName, thisTableColumn, KEY_ID);
    }

    /**
     * 关联查询 左外连接
     *
     * @param joinTableName leftOuterJoin表
     * @param asName        别名
     * @param onSql         on条件
     * @return
     */
    public Sql leftOuterJoin(String joinTableName, String asName, String onSql) {
        if (isOracle()) {
            addJoinSql(KEY_LEFT_OUTER_JOIN, processTableNameByDbType(joinTableName) + KEY_WHITESPACE + asName + KEY_ON + onSql);
        } else if (isDM()) {
            addJoinSql(KEY_LEFT_OUTER_JOIN, processTableNameByDbType(joinTableName) + KEY_AS + asName + KEY_ON + processDmOnsql(onSql));
        } else {
            addJoinSql(KEY_LEFT_OUTER_JOIN, processTableNameByDbType(joinTableName) + KEY_AS + asName + KEY_ON + onSql);
        }
        return this;
    }

    /**
     * 关联查询 右外连接
     *
     * @param modelClass model类
     * @param asName     别名
     * @param onSql      on条件
     * @return
     */
    @SuppressWarnings("rawtypes")
    public Sql rightOuterJoin(Class<? extends Model> modelClass, String asName, String onSql) {
        return rightOuterJoin(processTableName(modelClass), asName, onSql);
    }

    /**
     * 关联查询 右外连接
     *
     * @param modelClass      model类
     * @param asName          别名
     * @param thisTableColumn 主表持有关联列 （逻辑外键名）
     * @param joinTableColumn 关联表中的对应列名（一般是ID）
     * @return
     */
    @SuppressWarnings("rawtypes")
    public Sql rightOuterJoin(Class<? extends Model> modelClass, String asName, String thisTableColumn, String joinTableColumn) {
        return rightOuterJoin(processTableName(modelClass), asName, thisTableColumn, joinTableColumn);
    }

    /**
     * 关联查询 右外连接 on条件去是ID
     *
     * @param modelClass      model类
     * @param asName          别名
     * @param thisTableColumn 主表持有关联列 （逻辑外键名）
     * @return
     */
    @SuppressWarnings("rawtypes")
    public Sql rightOuterJoinOnId(Class<? extends Model> modelClass, String asName, String thisTableColumn) {
        return rightOuterJoin(modelClass, asName, thisTableColumn, KEY_ID);
    }

    /**
     * 关联查询 右外连接
     *
     * @param joinTableName   rightOuter表
     * @param asName          别名
     * @param thisTableColumn 主表持有关联列 （逻辑外键名）
     * @param joinTableColumn 关联表中的对应列名（一般是ID）
     * @return
     */
    public Sql rightOuterJoin(String joinTableName, String asName, String thisTableColumn, String joinTableColumn) {
        return rightOuterJoin(joinTableName, asName, processOnSql(asName, thisTableColumn, joinTableColumn));
    }


    /**
     * 关联查询 右外连接 on条件是ID
     *
     * @param joinTableName   rightOuter表
     * @param asName          别名
     * @param thisTableColumn 主表持有关联列 （逻辑外键名）
     * @return
     */
    public Sql rightOuterJoinOnId(String joinTableName, String asName, String thisTableColumn) {
        return rightOuterJoin(joinTableName, asName, processOnSql(asName, thisTableColumn, KEY_ID));
    }

    /**
     * 关联查询 右外连接
     *
     * @param joinTableName rightOUter html
     * @param asName        别名
     * @param onSql         onsql
     * @return
     */
    public Sql rightOuterJoin(String joinTableName, String asName, String onSql) {
        if (isOracle()) {
            addJoinSql(KEY_RIGHT_OUTER_JOIN, processTableNameByDbType(joinTableName) + KEY_WHITESPACE + asName + KEY_ON + onSql);
        } else if (isDM()) {
            addJoinSql(KEY_RIGHT_OUTER_JOIN, processTableNameByDbType(joinTableName) + KEY_AS + asName + KEY_ON + processDmOnsql(onSql));
        } else {
            addJoinSql(KEY_RIGHT_OUTER_JOIN, processTableNameByDbType(joinTableName) + KEY_AS + asName + KEY_ON + onSql);
        }
        return this;
    }

    /**
     * 关联查询 全连接
     *
     * @param joinTableName rightOUter html
     * @param asName        别名
     * @param onSql         onsql
     * @return
     */
    public Sql fullJoin(String joinTableName, String asName, String onSql) {
        if (isOracle()) {
            addJoinSql(KEY_FULL_JOIN, processTableNameByDbType(joinTableName) + KEY_WHITESPACE + asName + KEY_ON + onSql);
        } else if (isDM()) {
            addJoinSql(KEY_FULL_JOIN, processTableNameByDbType(joinTableName) + KEY_AS + asName + KEY_ON + processDmOnsql(onSql));
        } else {
            addJoinSql(KEY_FULL_JOIN, processTableNameByDbType(joinTableName) + KEY_AS + asName + KEY_ON + onSql);
        }
        return this;
    }

    /**
     * 关联查询 全连接
     *
     * @param joinTableName   fulljoin表
     * @param asName          别名
     * @param thisTableColumn 主表持有关联列 （逻辑外键名）
     * @param joinTableColumn 关联表中的对应列名（一般是ID）
     */
    public Sql fullJoin(String joinTableName, String asName, String thisTableColumn, String joinTableColumn) {
        return fullJoin(joinTableName, asName, processOnSql(asName, thisTableColumn, joinTableColumn));
    }


    /**
     * 关联查询 全连接 on条件是id
     *
     * @param joinTableName   fulljoin表
     * @param asName          别名
     * @param thisTableColumn 主表持有关联列 （逻辑外键名）
     */
    public Sql fullJoinOnId(String joinTableName, String asName, String thisTableColumn) {
        return fullJoin(joinTableName, asName, thisTableColumn, KEY_ID);
    }

    /**
     * 关联查询  全连接
     *
     * @param modelClass model类
     * @param asName     别名
     * @param onSql      on条件
     * @return
     */
    @SuppressWarnings("rawtypes")
    public Sql fullJoin(Class<? extends Model> modelClass, String asName, String onSql) {
        return fullJoin(processTableName(modelClass), asName, onSql);
    }

    /**
     * 关联查询  全连接
     *
     * @param modelClass      model类
     * @param asName          别名
     * @param thisTableColumn 主表持有关联列 （逻辑外键名）
     * @param joinTableColumn 关联表中的对应列名（一般是ID）
     * @return
     */
    @SuppressWarnings("rawtypes")
    public Sql fullJoin(Class<? extends Model> modelClass, String asName, String thisTableColumn, String joinTableColumn) {
        return fullJoin(processTableName(modelClass), asName, thisTableColumn, joinTableColumn);
    }


    /**
     * 关联查询  全连接
     *
     * @param modelClass      model类
     * @param asName          别名
     * @param thisTableColumn 主表持有关联列 （逻辑外键名）
     * @param joinTableColumn 关联表中的对应列名（一般是ID）
     * @return
     */
    @SuppressWarnings("rawtypes")
    public Sql fullJoinOnId(Class<? extends Model> modelClass, String asName, String thisTableColumn, String joinTableColumn) {
        return fullJoin(processTableName(modelClass), asName, thisTableColumn, joinTableColumn);
    }


    /**
     * 关联查询  全连接  on条件ID
     *
     * @param modelClass      model类
     * @param asName          别名
     * @param thisTableColumn 主表持有关联列 （逻辑外键名）
     * @return
     */
    @SuppressWarnings("rawtypes")
    public Sql fullJoinOnId(Class<? extends Model> modelClass, String asName, String thisTableColumn) {
        return fullJoin(modelClass, asName, thisTableColumn, KEY_ID);
    }


    /**
     * 左括号
     *
     * @return
     */
    public Sql bracketLeft() {
        processAnd();
        conditions.add(new Condition().bracketLeft());
        return this;
    }

    /**
     * 右括号
     *
     * @return
     */
    public Sql bracketRight() {
        conditions.add(new Condition().bracketRight());
        return this;
    }

    /**
     * 大于 比较 key>value
     *
     * @param columnName
     * @param value
     * @return
     */
    public Sql gt(String columnName, Object value) {
        return gt(columnName, value, true);
    }

    /**
     * 大于 比较 key>value
     *
     * @param columnName
     * @param value
     * @param ignoreIfValueIsNull
     * @return
     */
    public Sql gt(String columnName, Object value, boolean ignoreIfValueIsNull) {
        if (StrKit.isBlank(columnName)) {
            throw new RuntimeException("sql gt 'columnName' is null");
        }
        if (value == null) {
            if (ignoreIfValueIsNull) {
                return this;
            }
            throw new RuntimeException("sql gt 'value' is null");
        }
        processAnd();
        conditions.add(new Condition(processPrependMainTableAsName(columnName), value, Condition.GT));
        return this;
    }

    /**
     * 大于 比较 key>? 可多个
     *
     * @param columnNames
     * @return
     */
    public Sql gtQM(String... columnNames) {
        if (columnNames != null && columnNames.length > 0) {
            for (String columnName : columnNames) {
                gt(columnName, KEY_QUESTION_MARK);
            }
        }
        return this;
    }

    /**
     * 小于 比较 key<value
     *
     * @param columnName
     * @param value
     * @return
     */
    public Sql lt(String columnName, Object value) {
        return lt(columnName, value, true);
    }

    /**
     * 小于 比较 key<value
     *
     * @param columnName
     * @param value
     * @param ignoreIfValueIsNull
     * @return
     */
    public Sql lt(String columnName, Object value, boolean ignoreIfValueIsNull) {
        if (StrKit.isBlank(columnName)) {
            throw new RuntimeException("sql lt 'columnName' is null");
        }
        if (value == null) {
            if (ignoreIfValueIsNull) {
                return this;
            }
            throw new RuntimeException("sql lt 'value' is null");
        }
        processAnd();
        conditions.add(new Condition(processPrependMainTableAsName(columnName), value, Condition.LT));
        return this;
    }

    /**
     * 小于 比较 key<? 可多个
     *
     * @param columnNames
     * @return
     */
    public Sql ltQM(String... columnNames) {
        if (columnNames != null && columnNames.length > 0) {
            for (String columnName : columnNames) {
                lt(columnName, KEY_QUESTION_MARK);
            }
        }
        return this;
    }

    /**
     * 大于等于 比较 key>=value
     *
     * @param columnName
     * @param value
     * @return
     */
    public Sql ge(String columnName, Object value) {
        return ge(columnName, value, true);
    }

    /**
     * 大于等于 比较 key>=value
     *
     * @param columnName
     * @param value
     * @param ignoreIfValueIsNull
     * @return
     */
    public Sql ge(String columnName, Object value, boolean ignoreIfValueIsNull) {
        if (StrKit.isBlank(columnName)) {
            throw new RuntimeException("sql ge 'columnName' is null");
        }
        if (value == null) {
            if (ignoreIfValueIsNull) {
                return this;
            }
            throw new RuntimeException("sql ge 'value' is null");
        }
        processAnd();
        conditions.add(new Condition(processPrependMainTableAsName(columnName), value, Condition.GE));
        return this;
    }

    /**
     * 大于等于 比较 key>=? 可多个
     *
     * @param columnNames
     * @return
     */
    public Sql geQM(String... columnNames) {
        if (columnNames != null && columnNames.length > 0) {
            for (String columnName : columnNames) {
                ge(columnName, KEY_QUESTION_MARK);
            }
        }
        return this;
    }

    /**
     * 小于等于 比较 key<=value
     *
     * @param columnName
     * @param value
     * @return
     */
    public Sql le(String columnName, Object value) {
        return le(columnName, value, true);
    }

    /**
     * 小于等于 比较 key<=value
     *
     * @param columnName
     * @param value
     * @param ignoreIfValueIsNull
     * @return
     */
    public Sql le(String columnName, Object value, boolean ignoreIfValueIsNull) {
        if (StrKit.isBlank(columnName)) {
            throw new RuntimeException("sql le 'columnName' is null");
        }
        if (value == null) {
            if (ignoreIfValueIsNull) {
                return this;
            }
            throw new RuntimeException("sql le 'value' is null");
        }
        processAnd();
        conditions.add(new Condition(processPrependMainTableAsName(columnName), value, Condition.LE));
        return this;
    }

    /**
     * 小于等于 比较 key<=? 可多个
     *
     * @param columnNames
     * @return
     */
    public Sql leQM(String... columnNames) {
        if (columnNames != null && columnNames.length > 0) {
            for (String columnName : columnNames) {
                le(columnName, KEY_QUESTION_MARK);
            }
        }
        return this;
    }

    /**
     * 不等于 比较 key!=value
     *
     * @param columnName
     * @param value
     * @return
     */
    public Sql notEq(String columnName, Object value) {
        return notEq(columnName, value, true);
    }

    /**
     * 不等于 比较 key!=value
     *
     * @param columnName
     * @param value
     * @param ignoreIfValueIsNull
     * @return
     */
    public Sql notEq(String columnName, Object value, boolean ignoreIfValueIsNull) {
        if (StrKit.isBlank(columnName)) {
            throw new RuntimeException("sql notEq 'columnName' is null");
        }
        if (value == null) {
            if (ignoreIfValueIsNull) {
                return this;
            }
            throw new RuntimeException("sql notEq 'value' is null");
        }
        processAnd();
        conditions.add(new Condition(processPrependMainTableAsName(columnName), value, Condition.NOT_EQ));
        return this;
    }

    /**
     * 不等于 比较 id!=value
     *
     * @param value
     * @return
     */
    public Sql idNotEq(Object value) {
        return notEq(KEY_ID, value, false);
    }

    /**
     * 不等于 比较  key!=?
     *
     * @param columnNames
     * @return
     */
    public Sql notEqQM(String... columnNames) {
        if (columnNames != null && columnNames.length > 0) {
            for (String columnName : columnNames) {
                notEq(columnName, KEY_QUESTION_MARK);
            }
        }
        return this;
    }

    /**
     * 不等于比较 id!=?
     *
     * @return
     */
    public Sql idNotEqQM() {
        return notEqQM(KEY_ID);
    }

    /**
     * 不等于比较 pid!=?
     *
     * @return
     */
    public Sql pidNotEqQM() {
        return notEqQM(KEY_PID);
    }

    /**
     * 不等于比较 id!=?
     * 指定主键名
     *
     * @param idName
     * @return
     */
    public Sql idNotEqQM(String idName) {
        return notEqQM(idName);
    }

    /**
     * 不等于比较 pid!=?
     * 指定父主键名
     *
     * @param pidName
     * @return
     */
    public Sql pidNotEqQM(String pidName) {
        return notEqQM(pidName);
    }

    /**
     * 更新sql专用 set字段值
     *
     * @param columnName
     * @param value
     * @return
     */
    public Sql set(String columnName, Object value) {
        if (updateColumnsMap == null) {
            updateColumnsMap = new LinkedHashMap<String, Object>();
        }
        updateColumnsMap.put(processPrependMainTableAsName(columnName), processObjectValue(value));
        return this;
    }

    /**
     * 更新sql专用 set字段值是个表达式
     *
     * @param columnName
     * @param sqlExpressStr
     * @return
     */
    public Sql setSqlExpress(String columnName, String sqlExpressStr) {
        if (updateColumnsMap == null) {
            updateColumnsMap = new LinkedHashMap<String, Object>();
        }
        updateColumnsMap.put(processPrependMainTableAsName(columnName), new SqlExpress(sqlExpressStr));
        return this;
    }

    /**
     * 根据数据库类型处理名称 专门针对DM
     *
     * @param name
     * @return
     */
    private String processNameIfDM(String name) {
        if (!DBType.isDM(dbType) || name.contains(KEY_STAR)) {
            return name;
        }
        return KEY_QUOTATION_MARKS + name + KEY_QUOTATION_MARKS;
    }

    /**
     * 根据数据库类型处理名称 专门针对DM
     *
     * @param name
     * @return
     */
    private String processNameIfMySql(String name) {
        if (!DBType.isMysql(dbType) || name.contains(KEY_STAR)) {
            return name;
        }
        return KEY_MYSQL_SINGLE_QUOTES + name + KEY_MYSQL_SINGLE_QUOTES;
    }

    /**
     * 处理object value
     *
     * @param value
     * @return
     */
    private Object processObjectValue(Object value) {
        if (value == null) {
            return value;
        }
        if (value instanceof Date) {
            return toDateTime((Date) value);
        } else if (value instanceof Time) {
            return toTime((Time) value);
        }
        return value;
    }

    /**
     * 将对象转sql
     *
     * @return
     */
    public String toSql() {
        return toSql(new StringBuilder());
    }

    /**
     * 将对象转sql
     *
     * @param sql
     * @return
     */
    public String toSql(StringBuilder sql) {
        if (type == TYPE_NONE) {
            select();
        }
        //检测where条件必要性 update和delete检测 默认就得检测除非手动关闭
        checkWhereMustExists();
        checkUpdateSets();
        switch (type) {
            case TYPE_SELECT:
                sql.append(KEY_SELECT);
                break;
            case TYPE_INSERT:
                sql.append(KEY_INSERT);
                break;
            case TYPE_INSERT_BATCH:
                sql.append(KEY_INSERT);
                break;
            case TYPE_UPDATE:
                sql.append(KEY_UPDATE);
                break;
            case TYPE_DELETE:
                sql.append(KEY_DELETE).append(KEY_FROM);
                break;
        }
        builderSql(sql, false);
        return sql.toString();
    }

    private void checkUpdateSets() {
        if (type == TYPE_UPDATE && (updateColumnsMap == null || updateColumnsMap.isEmpty())) {
            throw new RuntimeException("update columns is empty");
        }
    }


    public boolean isCheckWhereExists() {
        return checkWhereExists;
    }

    public Sql setCheckWhereExists(boolean checkWhereExists) {
        this.checkWhereExists = checkWhereExists;
        return this;
    }

    /**
     * 检测where条件必须存在
     * update和delete才需要检测
     */
    private void checkWhereMustExists() {
        if (checkWhereExists && (type == TYPE_UPDATE || type == TYPE_DELETE) && conditions.isEmpty()) {
            if (type == TYPE_UPDATE) {
                throw new RuntimeException("update sql where条件为空");
            } else {
                throw new RuntimeException("delete sql where条件为空");
            }
        }
    }

    /**
     * 内部底层函数 将对象属性判断拼接 转为最终sql
     *
     * @param sql
     * @param withoutSelect 是否生成不带select的sql
     */
    private void builderSql(StringBuilder sql, boolean withoutSelect) {
        if (prepared) {
            whereValues = new ArrayList<Object>();
        }
        // 首先判断是不是查询类型语句 是的话 加 from关键词
        if (type == TYPE_SELECT) {
            if (!withoutSelect) {
                sql.append(returnColumns);
            }
            sql.append(KEY_FROM);
        }
        // 拼接tableName
        sql.append(processTableNameByDbType(table));
        if (this.mainTableAsName != null && !this.mainTableAsName.isEmpty()) {
            sql.append(KEY_WHITESPACE).append(mainTableAsName).append(KEY_WHITESPACE);
        }
//        switch (dbType) {
//            case DBType.MYSQL:
//
//                break;
//            case DBType.ORACLE:
//                sql.append(table);
//                break;
//            case DBType.SQLSERVER:
//                sql.append(table);
//                break;
//            case DBType.POSTGRESQL:
//                sql.append(table);
//                break;
//            case DBType.DM:
//                sql.append(processTableNameByDbType(table))
//                if(table.startsWith(KEY_BRACKET_LEFT)||table.contains(KEY_QUOTATION_MARKS)){
//                    sql.append(table);
//                }else{
//                    sql.append(KEY_QUOTATION_MARKS).append(table).append(KEY_QUOTATION_MARKS);
//                }
//                break;
//        }
        if ((type == TYPE_SELECT || type == TYPE_UPDATE) && joinSqls != null && !joinSqls.isEmpty()) {
            joinSqls.forEach(sql::append);
        }
        // 判断更新类型的语句 更新字段是否存在设置 如果存在 拼接set
        if (type == TYPE_UPDATE && updateColumnsMap != null) {
            int size = updateColumnsMap.size();
            int index = 0;
            sql.append(KEY_SET);
            Object v;
            for (Entry<String, Object> e : updateColumnsMap.entrySet()) {
                sql.append(e.getKey()).append("=");
                v = e.getValue();
                if (v instanceof String) {
                    sql.append(safeValue(v.toString()));
                } else if (v instanceof Boolean) {
                    sql.append(SqlUtil.boolToChar((Boolean) v));
                } else if (v instanceof SqlExpress) {
                    sql.append(v);
                } else {
                    sql.append(v);
                }
                if (index != size - 1) {
                    sql.append(KEY_COMMA);
                }
                index++;
            }
        } else if (this.type == TYPE_INSERT) {
            // 插入语句设置
            sql.append(KEY_WHITESPACE).append(String.format(KEY_INSERT_VALUES, processInsertValuesToString()));
        }
        // 判断吃否存在where过滤条件 拼接where后面的查询条件
        if (!conditions.isEmpty()) {
            sql.append(KEY_WHERE);
            // 循环查询条件
            for (Condition condition : conditions) {
                sql.append(condition.toSql(dbType, prepared));
                // 判断当前设置是否使用问号占位参数
                if (prepared && condition.getType() == Condition.TYPE_COMPARE && !condition.getCompareState().contains("in")) {
                    whereValues.add(SqlUtil.processBooleanValue(condition.getValue1()));
                }
            }

        }

        // 判断分组查询
        if (isGroupBy()) {
            sql.append(KEY_GROUPBY);
            if (groupBys.length == 1) {
                sql.append(groupBys[0]);
            } else {
                sql.append(JBoltArrayUtil.join(groupBys));
            }
        }

        //判断处理having
        if (isHaving()) {
            sql.append(KEY_HAVING);
            sql.append(havingSqlExpress);
        }

        // 判断排序
        if (orderBys != null && orderBys.size() > 0) {
            sql.append(KEY_ORDERBY);
            int size = orderBys.size();
            for (int i = 0; i < size; i++) {
                sql.append(orderBys.get(i).toString());
                if (i < size - 1) {
                    sql.append(KEY_COMMA);
                }
            }
        }
        // 判断limit
        if (hasPage) {
            sql.append(KEY_WHITESPACE);
            switch (dbType) {
                case DBType.MYSQL:
                    processMysqlPage(sql);
                    break;
                case DBType.ORACLE:
                    processOraclePage(sql);
                    break;
                case DBType.SQLSERVER:
                    processSqlserverPage(sql);
                    break;
                case DBType.POSTGRESQL:
                    processPostgresqlPage(sql);
                    break;
                case DBType.DM:
                    processDMPage(sql);
                    break;
            }
        }
        //判断是否存在Union
        if (hasUnion()) {
            processUnion(sql);
        }
        //处理select count group by Sql
        if (isGroupByAndQueryCount()) {
            sql.insert(0, "select count(*) from(");
            sql.append(") jbolt_tmp_count_table");
        }
    }

    /**
     * 处理数据库表名
     *
     * @param tableName
     * @return
     */
    private String processTableNameByDbType(String tableName) {
        if (tableName.contains(KEY_DOT) || tableName.contains(KEY_BRACKET_LEFT) || tableName.contains(KEY_SQUARE_BRACKETS_LEFT) || tableName.contains(KEY_SINGLE_QUOTES)) {
            return tableName;
        }
        switch (dbType) {
            case DBType.MYSQL:
                if (tableName.contains(KEY_MYSQL_SINGLE_QUOTES)) {
                    return tableName;
                }
                return KEY_MYSQL_SINGLE_QUOTES + tableName + KEY_MYSQL_SINGLE_QUOTES;
            case DBType.SQLSERVER:
                if (tableName.contains(KEY_SQUARE_BRACKETS_LEFT)) {
                    return tableName;
                }
                return KEY_SQUARE_BRACKETS_LEFT + tableName + KEY_SQUARE_BRACKETS_RIGHT;

            case DBType.ORACLE:
                break;
            case DBType.POSTGRESQL:
            case DBType.DM:
                // name as xxx
                if (tableName.contains(KEY_QUOTATION_MARKS)) {
                    return tableName;
                }
                if (StrKit.isBlank(schema)) {
                    return KEY_QUOTATION_MARKS + tableName + KEY_QUOTATION_MARKS;
                }
                return KEY_QUOTATION_MARKS + schema + KEY_QUOTATION_MARKS + KEY_DOT + KEY_QUOTATION_MARKS + tableName + KEY_QUOTATION_MARKS;
            default:
                break;
        }
        return tableName;
    }

    /**
     * 处理Union sql
     *
     * @param sql
     */
    private void processUnion(StringBuilder sql) {
        for (UnionSql unionSql : unionSqls) {
            unionSql.appendToSql(sql);
        }
    }

    /**
     * 处理sqlServer分页
     *
     * @param sql
     */
    private void processSqlserverPage(StringBuilder sql) {
        if (pageNumber == 1 && pageSize == 1) {
            return;
        }
        int end = pageNumber * pageSize;
        if (end <= 0) {
            end = pageSize;
        }
        int begin = (pageNumber - 1) * pageSize;
        if (begin < 0) {
            begin = 0;
        }

        String findSql = sql.toString();
        sql.setLength(0);
        sql.append("SELECT *  FROM ( SELECT row_number() over (order by tempcolumn) temprownumber, * FROM ");

        if (StrUtil.containsIgnoreCase(findSql, KEY_DISTINCT)) {
            sql.append(" ( SELECT ").append(KEY_DISTINCT).append(" TOP ").append(end);
            findSql = StrUtil.replaceFirst(findSql, KEY_DISTINCT, KEY_WHITESPACE, true);
        } else {
            sql.append(" ( SELECT TOP ").append(end);
        }
        sql.append(" tempcolumn=0,");
        findSql = StrUtil.replaceFirst(findSql, KEY_SELECT, KEY_WHITESPACE, true);
        sql.append(findSql);
        sql.append(")vip)mvp where temprownumber>").append(begin);


    }

    /**
     * 处理oracle分页
     *
     * @param sql
     */
    private void processOraclePage(StringBuilder sql) {
        int start = (pageNumber - 1) * pageSize;
        int end = pageNumber * pageSize;
        String findSql = sql.toString();
        sql.setLength(0);
        sql.append("select ").append(processOracleJoinSearchReturnColumns(returnColumns)).append(" from ( select row_.*, rownum rownum_ from (  ");
        sql.append(findSql);
        sql.append(" ) row_ where rownum <= ").append(end).append(") table_alias");
        sql.append(" where table_alias.rownum_ > ").append(start);
    }

    /**
     * 特殊处理oracle的select(returnColumns)
     *
     * @param returnColumns
     * @return
     */
    public String processOracleJoinSearchReturnColumns(String returnColumns) {
        //如果是KEY_STAR 直接返回 ， 包含KEY_STAR 直接返回KEY_STAR
        if (KEY_STAR.equals(returnColumns) || returnColumns.contains(KEY_STAR)) {
            return KEY_STAR;
        }
        //单个
        if (columns.length == 1) {
            //如果只有一个字段 需要处理 有联合查询xxx.yyy 和 as的情况 xxx.yyy as yyy 或者  yyy as y
            return clearReturnColumnsPrefixAndAs(returnColumns);
        }

        //挨个处理后合并为一个字符串
        String newReturnColumns = StreamUtil.join(Arrays.stream(columns).map(this::clearReturnColumnsPrefixAndAs), KEY_COMMA);
        if (StrKit.isBlank(newReturnColumns)) {
            throw new RuntimeException("Sql.java select(returnColumns)中 returnColumns格式异常:" + newReturnColumns);
        }
        return newReturnColumns;
    }

    /**
     * 清除xxx.yyy as yyy 里的as之前所有内容 如果没有as 也清除xxx.
     *
     * @param returnColumns
     * @return
     */
    private String clearReturnColumnsPrefixAndAs(String returnColumns) {
        int index = StrUtil.lastIndexOfIgnoreCase(returnColumns, KEY_AS);
        //如果有括号 必须要as
        if (returnColumns.contains(KEY_BRACKET_LEFT) && returnColumns.lastIndexOf(KEY_BRACKET_RIGHT) != -1 && index == -1) {
            throw new RuntimeException("Sql.java select(returnColumns)中 returnColumns格式异常:" + returnColumns + ",如果使用了函数 请增加 as xxx");
        }
//		int comIndex = returnColumns.indexOf(KEY_COMMA);
//		if(comIndex!=-1) {
//			//说明有逗号 如果逗号前面有)或者后面有(就不行
//			if(returnColumns.substring(comIndex).indexOf(KEY_BRACKET_LEFT)!=-1 || returnColumns.substring(0,comIndex).indexOf(KEY_BRACKET_RIGHT)!=-1) {
//				throw new RuntimeException("Sql.java select(returnColumns)中 returnColumns格式异常:"+returnColumns+",如果定义多列，请使用此方式定义:select(col1,col2,col3)");
//			}
//		}

        if (index == -1) {
            index = returnColumns.lastIndexOf(KEY_WHITESPACE);
            if (index != -1) {
                index = index + 1;
            }
        } else {
            index = index + KEY_AS.length();
        }
        if (index == -1) {
            int dotIndex = returnColumns.indexOf(KEY_DOT);
            if (dotIndex == -1) {
                return returnColumns;
            }
            returnColumns = returnColumns.substring(dotIndex + 1);
            if (StrKit.isBlank(returnColumns)) {
                throw new RuntimeException("Sql.java select(returnColumns)中的returnColumns格式异常:" + returnColumns);
            }
            return returnColumns;
        }
        returnColumns = returnColumns.substring(index);
        if (StrKit.isBlank(returnColumns)) {
            throw new RuntimeException("Sql.java select(returnColumns)中的returnColumns格式异常:" + returnColumns);
        }
        return returnColumns;
    }

    /**
     * 清除别名前面的内容
     *
     * @param returnColumns
     * @return
     */
    @SuppressWarnings("unused")
    private String clearReturnColumnsAs(String returnColumns) {
        int index = StrUtil.lastIndexOfIgnoreCase(returnColumns, " as ");
        if (index == -1) {
            index = returnColumns.lastIndexOf(" ");
            if (index != -1) {
                index = index + 1;
            }
        } else {
            index = index + 4;
        }
        if (index == -1) {
            return returnColumns;
        }
        returnColumns = returnColumns.substring(index);
        if (StrKit.isBlank(returnColumns)) {
            throw new RuntimeException("Sql.java select(returnColumns)中的returnColumns格式异常:" + returnColumns);
        }
        return returnColumns.trim();
    }


    /**
     * 处理mysql分页
     */
    private void processMysqlPage(StringBuilder sql) {
        int offset = (pageNumber - 1) * pageSize;
        sql.append(KEY_LIMIT).append(offset).append(KEY_COMMA).append(pageSize).append(KEY_WHITESPACE);
    }

    /**
     * 处理DM分页
     */
    private void processDMPage(StringBuilder sql) {
        int offset = (pageNumber - 1) * pageSize;
        sql.append(KEY_LIMIT).append(offset).append(KEY_COMMA).append(pageSize).append(KEY_WHITESPACE);
    }

    /**
     * 处理postgresql分页
     */
    private void processPostgresqlPage(StringBuilder sql) {
        int offset = (pageNumber - 1) * pageSize;
        sql.append(KEY_LIMIT).append(pageSize).append(KEY_OFFSET).append(offset).append(KEY_WHITESPACE);
    }

    /**
     * 将insertValues转为string 字符串
     *
     * @return
     */
    private String processInsertValuesToString() {
        if (insertValues == null || insertValues.length == 0) {
            return KEY_NULLSTRING;
        }
        StringBuilder sb = new StringBuilder();
        int len = insertValues.length;
        Object value;
        for (int i = 0; i < len; i++) {
            value = insertValues[i];
            if (value instanceof String) {
                sb.append(safeValue(value.toString()));
            } else if (value instanceof Boolean) {
                sb.append(SqlUtil.boolToChar((Boolean) value));
            } else if (value instanceof SqlExpress) {
                sb.append(value);
            } else {
                sb.append(value);
            }
            if (i < len - 1) {
                sb.append(KEY_COMMA);
            }
        }
        return sb.toString();
    }

    @Override
    public String toString() {
        return toSql();
    }

    /**
     * 计数 count(*)
     *
     * @return
     */
    public Sql count() {
        return count(KEY_STAR);
    }

    /**
     * 对指定列计数
     *
     * @param column
     * @return
     */
    public Sql count(String column) {
        this.queryCount = true;
        processReturnColumnsBySelectCount(column);
        this.select(returnColumns);
        return this;
    }

    private void processReturnColumnsBySelectCount(String column) {
        if (StrKit.isBlank(column)) {
            column = KEY_STAR;
        }
        if (!column.contains(KEY_STAR)) {
            column = processPrependMainTableAsName(column);
        }
        if (this.queryDistinctCount) {
            returnColumns = returnColumns + KEY_COMMA + String.format(KEY_COUNT_COLUMN, column);
        } else {
            returnColumns = String.format(KEY_COUNT_COLUMN, column);
        }

    }

    /**
     * 对指定列查询最大值
     *
     * @param column
     * @return
     */
    public Sql max(String column) {
        this.queryMax = true;
        processReturnColumnsBySelectFormat(column, KEY_MAX_COLUMN);
        this.select(returnColumns);
        return this;
    }

    /**
     * 对指定列查询最小值
     *
     * @param column
     * @return
     */
    public Sql min(String column) {
        this.queryMin = true;
        processReturnColumnsBySelectFormat(column, KEY_MIN_COLUMN);
        this.select(returnColumns);
        return this;
    }

    /**
     * 对指定列求和
     *
     * @param column
     * @return
     */
    public Sql sum(String column) {
        this.queryMin = true;
        processReturnColumnsBySelectFormat(column, KEY_SUM_COLUMN);
        this.select(returnColumns);
        return this;
    }

    /**
     * 对指定列求平均
     *
     * @param column
     * @return
     */
    public Sql avg(String column) {
        this.queryMin = true;
        processReturnColumnsBySelectFormat(column, KEY_AVG_COLUMN);
        this.select(returnColumns);
        return this;
    }

    private void processReturnColumnsBySelectFormat(String column, String format) {
        if (StrKit.isBlank(column)) {
            column = KEY_STAR;
        }
        if (!column.equals(KEY_STAR)) {
            column = processPrependMainTableAsName(column);
        }
        returnColumns = String.format(format, column);

    }

    /**
     * 不重复列
     *
     * @param distinctColumns
     * @return
     */
    public Sql distinct(String... distinctColumns) {
        if (distinctColumns == null || distinctColumns.length == 0) {
            throw new RuntimeException("distinct columns is null");
        }

        if (distinctColumns.length == 1) {
            String columnStr = distinctColumns[0];
            this.distinctColumn = columnStr;
            if (columnStr.contains(KEY_COMMA)) {
                this.select(JBoltArrayUtil.from(KEY_DISTINCT + columnStr));
            } else {
                this.select(KEY_DISTINCT + this.distinctColumn);
            }
        } else {
            this.distinctColumn = JBoltArrayUtil.join(distinctColumns);
            this.select(JBoltArrayUtil.from(KEY_DISTINCT + this.distinctColumn));
        }
        this.queryDistinct = true;
        return this;
    }
//    /**
//     * 不重复列
//     *
//     * @param column
//     * @param otherColumns
//     * @return
//     */
//    public Sql distinct(String distinctColumn,String... otherColumns) {
//        if(StrKit.isBlank(distinctColumn)){
//            throw new RuntimeException("distinct column is null");
//        }
//        if(distinctColumn.contains(KEY_STAR)){
//            throw new RuntimeException("distinct column contains *");
//        }
//        if(distinctColumn.contains(KEY_COMMA)){
//            throw new RuntimeException("distinct column contains ,");
//        }
//        this.distinctColumn = distinctColumn;
//        if(otherColumns == null || otherColumns.length==0){
//            this.select(KEY_DISTINCT + distinctColumn);
//        }else if(otherColumns.length == 1){
//            String otherColumnStr = otherColumns[0];
//            if(otherColumnStr.contains(KEY_COMMA)){
//                otherColumns = JBoltArrayUtil.from(KEY_DISTINCT + distinctColumn+KEY_COMMA+otherColumnStr);
//            }
//            this.select(otherColumns);
//        }else{
//            List<String> list = ListUtil.toList(otherColumns);
//            list.add(0,KEY_DISTINCT + distinctColumn);
//            this.select(list);
//        }
//        this.queryDistinct = true;
//        return this;
//    }

    /**
     * 查询转数量
     *
     * @return
     */
    public Sql distinctToDistinctCount() {
        if (!isQueryDistinct()) {
            return this;
        }
        return distinctCount(distinctColumn);
    }

    /**
     * 不重复列计数
     *
     * @param column
     * @return
     */
    public Sql distinctCount(String column) {
        this.queryDistinctCount = true;
        processReturnColumnsBySelectDistinctCount(column);
        this.select(returnColumns);
        return this;
    }

    private void processReturnColumnsBySelectDistinctCount(String column) {
        Objects.requireNonNull(column, "Sql processReturnColumnsBySelectDistinctCount column is null");
        if (column.contains(KEY_STAR)) {
            throw new RuntimeException("Sql distinctCount column can not contains *,Please specify columns");
        }
        column = processPrependMainTableAsName(column);
        if (column.contains(KEY_COMMA)) {
            //如果带着逗号 删除逗号之后的内容
            column = column.substring(0, column.indexOf(KEY_COMMA));
        }

        if (this.queryCount) {
            returnColumns = returnColumns + KEY_COMMA + String.format(KEY_COUNT_DISTINCT, column);
        } else {
            returnColumns = String.format(KEY_COUNT_DISTINCT, column);
        }
    }

    /**
     * 条件 exists(sql对象)
     *
     * @param sql
     * @return
     */
    public Sql exists(Sql sql) {
        if (sql == null) {
            return this;
        }
        processAnd();
        conditions.add(new Condition().exists(sql.toSql()));
        return this;
    }

    /**
     * 条件 not exists(sql对象)
     *
     * @param sql
     * @return
     */
    public Sql notExists(Sql sql) {
        if (sql == null) {
            return this;
        }
        processAnd();
        conditions.add(new Condition().notExists(sql.toSql()));
        return this;
    }

    public static void main(String[] args) {
//		Sql sql = Sql.oracle().select("u.username", "concat('a', concat(g.login_ip,'IP:')) as ip").from("jb_user", "u").leftJoin("jb_login_log", "g", "u.id=g.id");
//		sql.page(1, 10);
//		Sql sql=Sql.mysql()
//				.update("user")
//				.set("xxx", new Date());
//
//
//		System.out.println(sql);
//		Sql sql = Sql.oracle().select("u.username,concat('IP:',g.login_ip) as ip").from("jb_user", "u").leftJoin("jb_login_log", "g", "u.id=g.id");
//		sql.page(1, 10);
//		System.out.println(sql);
    }

    public String getCountColumns() {

        return countColumns;
    }

    public String getReturnColumns() {

        return returnColumns;
    }

    public void setCountColumns(String countColumns) {
        this.countColumns = countColumns;
    }

    public boolean hasTable() {
        return table != null && table.length() > 0;
    }

    public boolean hasMainTableAsName() {
        return mainTableAsName != null && mainTableAsName.length() > 0;
    }

    /**
     * 得到参数集合
     *
     * @return
     */
    public Object[] getWhereValues() {
        if (prepared && whereValues != null && whereValues.size() > 0) {
            return whereValues.toArray();
        }
        return Collections.EMPTY_LIST.toArray();
    }

    public boolean isPrepared() {
        return prepared;
    }

    public boolean isQueryColumnList() {
        return queryColumnList;
    }

    public boolean isQueryCount() {
        return queryCount;
    }

    public boolean isSelect() {
        return type == TYPE_SELECT;
    }

    public boolean isQueryMax() {
        return queryMax;
    }

    public boolean isDelete() {
        return type == TYPE_DELETE;
    }

    public boolean isQuery() {
        return type == TYPE_SELECT;
    }

    public boolean isUpdate() {
        return type == TYPE_UPDATE;
    }

    public boolean isQueryDistinctCount() {
        return queryDistinctCount;
    }

    public boolean isQueryDistinct() {
        return queryDistinct;
    }

    /**
     * 生成不带select的sql
     *
     * @return
     */
    public String toSqlExceptSelect() {
        StringBuilder sql = new StringBuilder();
        builderSql(sql, true);
        return sql.toString();
    }

    /**
     * 得到select部分
     *
     * @return
     */
    public String getSelect() {
        StringBuilder select = new StringBuilder();
        select.append(KEY_SELECT).append(KEY_WHITESPACE).append(returnColumns);
        return select.toString();
    }

    /**
     * 切换dbType为mysql
     *
     * @return
     */
    public static Sql mysql() {
        return me(DBType.MYSQL);
    }

    /**
     * 设置schema dm常用 得用
     *
     * @param schema
     * @return
     */
    public Sql schema(String schema) {
        this.schema = schema;
        return this;
    }

    /**
     * 切换dbType为postgresql
     *
     * @return
     */
    public static Sql postgresql() {
        return me(DBType.POSTGRESQL);
    }

    /**
     * 切换dbType为DM
     *
     * @return
     */
    public static Sql dm() {
        return me(DBType.DM);
    }

    /**
     * 切换dbType为oracle
     *
     * @return
     */
    public static Sql oracle() {
        return me(DBType.ORACLE);
    }

    /**
     * 切换dbType为sqlserver
     *
     * @return
     */
    public static Sql sqlserver() {
        return me(DBType.SQLSERVER);
    }

    /**
     * 设置数据库类型
     *
     * @param dbType
     * @return
     */
    public Sql setDbType(String dbType) {
        if (StrKit.isBlank(dbType)) {
            String msg = "Sql.java 初始化 必须指定dbType";
            LogKit.error(msg);
            throw new RuntimeException(msg);
        }
        this.dbType = dbType;
        return this;
    }

    public Object[] getInsertValues() {
        return insertValues;
    }

    public void setInsertValues(Object[] insertValues) {
        this.insertValues = insertValues;
    }

    /**
     * 如果是pgsql 处理id正序排序
     *
     * @return
     */
    public Sql orderByIdAscIfPgSql() {
        if (isPostgresql()) {
            orderById();
        }
        return this;
    }

    /**
     * 字段自增+1
     *
     * @param columnName
     * @return
     */
    public Sql plus(String columnName) {
        return plus(columnName, 1);
    }

    /**
     * 字段自增+number
     *
     * @param columnName
     * @param number
     * @return
     */
    public Sql plus(String columnName, int number) {
        return set(columnName, new SqlExpress(processPrependMainTableAsName(columnName) + KEY_PLUS + number));
    }

    /**
     * 字段自增+number
     *
     * @param columnName
     * @param number
     * @return
     */
    public Sql plus(String columnName, BigDecimal number) {
        return set(columnName, new SqlExpress(processPrependMainTableAsName(columnName) + KEY_PLUS + number.toString()));
    }

    /**
     * 字段减1
     *
     * @param columnName
     * @return
     */
    public Sql minus(String columnName) {
        return minus(columnName, 1, null);
    }

    /**
     * 字段减1
     *
     * @param columnName
     * @param min
     * @return
     */
    public Sql minus(String columnName, Integer min) {
        return minus(columnName, 1, min);
    }

    /**
     * 字段减number
     *
     * @param columnName
     * @param number
     * @param min        最小值
     * @return
     */
    public Sql minus(String columnName, int number, Integer min) {
        if (min != null) {
            String ifKey = KEY_IF;
            if (isSqlServer()) {
                ifKey = KEY_IIF;
            }
            return set(columnName, new SqlExpress(ifKey + KEY_BRACKET_LEFT + processPrependMainTableAsName(columnName) + KEY_MINUS + number + KEY_LT + min + KEY_COMMA + min + KEY_COMMA + (processPrependMainTableAsName(columnName) + KEY_MINUS + number) + KEY_BRACKET_RIGHT));
        }
        return set(columnName, new SqlExpress(processPrependMainTableAsName(columnName) + KEY_MINUS + number));
    }

    /**
     * 字段减number
     *
     * @param columnName
     * @param number
     * @param min        最小值
     * @return
     */
    public Sql minus(String columnName, BigDecimal number, Integer min) {
        if (min != null) {
            String ifKey = KEY_IF;
            if (isSqlServer()) {
                ifKey = KEY_IIF;
            }
            return set(columnName, new SqlExpress(ifKey + KEY_BRACKET_LEFT + processPrependMainTableAsName(columnName) + KEY_MINUS + number.toString() + KEY_LT + min + KEY_COMMA + min + KEY_COMMA + (processPrependMainTableAsName(columnName) + KEY_MINUS + number) + KEY_BRACKET_RIGHT));
        }
        return set(columnName, new SqlExpress(processPrependMainTableAsName(columnName) + KEY_MINUS + number.toString()));
    }

    /**
     * 如果是pgsql 处理id倒序排序
     *
     * @return
     */
    public Sql orderByIdDescIfPgSql() {
        if (isPostgresql()) {
            orderById(true);
        }
        return this;
    }

    public int getPageNumber() {
        return pageNumber;
    }

    public int getPageSize() {
        return pageSize;
    }

    public boolean hasPage() {
        return hasPage;
    }

    public void clearPage() {
        this.hasPage = false;
        this.pageNumber = 0;
        this.pageSize = 0;
    }

    public String getMainTableAsName() {
        return mainTableAsName;
    }

    /**
     * 处理主表列名添加表的asName
     *
     * @param columnName
     */
    private String processPrependMainTableAsName(String columnName) {
        return processPrependMainTableAsName(columnName, isDM());
    }

    /**
     * 处理主表列名添加表的asName
     *
     * @param columnName
     * @param needProcessNameByDbType
     */
    private String processPrependMainTableAsName(String columnName, boolean needProcessNameByDbType) {
        String column = needProcessNameByDbType ? processColumnNameByDbType(columnName) : columnName;
        if (StrKit.isBlank(mainTableAsName) || columnName.contains(KEY_DOT) || columnName.contains(KEY_COMMA) || columnName.contains(KEY_STAR) || columnName.contains(KEY_BRACKET_LEFT)) {
            return column;
        }
        return mainTableAsName + KEY_DOT + column;
    }

    public String[] getColumns() {
        return columns;
    }

    public void setColumns(String[] columns) {
        this.columns = columns;
    }

    private String safeValue(String value) {
        if (value.contains(KEY_SINGLE_QUOTES)) {
            value = value.replace(KEY_SINGLE_QUOTES, KEY_SINGLE_QUOTES_2);
        }
        return KEY_SINGLE_QUOTES + value + KEY_SINGLE_QUOTES;
    }

    public List<OrderBy> getOrderBys() {
        return orderBys;
    }

    public boolean hasOrderColumns() {
        return orderBys != null && orderBys.size() > 0;
    }

    public void setOrderBys(List<OrderBy> orderBys) {
        this.orderBys = orderBys;
    }
}

